Really easy?

  • Thread starter Thread starter PaulSinki
  • Start date Start date
P

PaulSinki

Having a mind block, hoping someone can help me.
I want to be able to go to the furtherest right cell of a spreadsheet,
and then select every occupied cell in that range and run a loop over
it. I have an xlToRight code running me to the right-most cell, but I
can't sue xlDown as there are blank rows before the end. However,
selecting down to row 1600 would do the job. How can I make the
selection? The ActiveCell is Row 1 in the relevant column and I want to
select row 1 to row 1600 in this column.
Any help greatly appreciated!
 
Try this. You may have to adjust the SpecialCells line to get what yo
want (record an Edit/Goto macro).

'-------------------------------------------------
Sub SelectSpecial()
lastcol = ActiveSheet.Cells.Find(what:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
lastrow = ActiveSheet.Cells.Find(what:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
ActiveSheet.Range(Cells(1, lastcol), Cells(lastrow, lastcol)) _
.SpecialCells(xlCellTypeConstants, 1).Select
End Sub
'-------------------------------------------------
 
With Range("A1")
Range(.Offset(0,0), .SpecialCells(xlTypeLastCell)).Select
End With

or
For Each myCell In Range(Range("A1"), _
Range("A1").End(xlToRight).Offset(1600, 0).End(xlUp))
:
Next myCell


Kevin Beckham
 
Not sure what you're asking.

dim thisColumn as Long
dim maxrow as long

maxrow = 1600
thisColumn = ActiveCell.Column


For each Cell in Range(Cells(1,thisColumn), _
cells(maxrow,thisColumn))


Next

or
WITH Range(Cells(1,thisColumn), _
cells(maxrow,thisColumn))
''' use for example
.FormulaR1C1 = .....

End With


Patrick Molloy
Microsoft Excel MVP
 
Back
Top