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!


---
Message posted from http://www.ExcelForum.com/

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
'-------------------------------------------------

--
Message posted from http://www.ExcelForum.com

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

-----Original Message-----
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!


---
Message posted from http://www.ExcelForum.com/

.

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
-----Original Message-----
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!


---
Message posted from http://www.ExcelForum.com/

.