How do I determine the last cell in a Column

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I know how to get the last cell in a column:

range("A65536").End(xlUp).address for the row number
range("A65536").End(xlUp).row for the row number

but I am not sure how to translate this to doing the same for the row.
Any help with be appreciated. Thanks.
 
Try...
Range("IV1").End(xlToLeft).Column

I prefer to use...

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
 
Thanks Sheeloo. One more question, how do I use it in a for statement that
is looping through cells D14: T14 i.e.,

For Each c In Wsht.Range((4, 14), (4, lastColumn)).Cells
If c.Value = Range("B4") Then
getMSaddress = c.Address(ColumnAbsolute:=False)
Exit Function
End If
Next c
 
dim c as range
Dim myRng as range
with wsht
set myrng = .range(.cells(4,14), .cells(4,lastcol))
end with

for each c in myrng.cells
.....
 
Thanks Dave.

Dave Peterson said:
dim c as range
Dim myRng as range
with wsht
set myrng = .range(.cells(4,14), .cells(4,lastcol))
end with

for each c in myrng.cells
.....
Click to expand...
 
Try
Wsht.Range(Cells(14, 4), Cells(14, lastColumn)).Select
For Each c In Selection
If c.Value = Range("B4") Then
getMSaddress = c.Address
Exit For
End If
Next c
 
This could cause trouble.

If you're going to qualify .range, it's a good idea to qualify cells:

Wsht.Range(wsht.Cells(14, 4), wsht.Cells(14, lastColumn)).Select

And if wsht isn't the activesheet, then the .Select will fail. I wouldn't
select the range to work on it.
 
Back
Top