Assigning a row to a variable

T

Todd

Hi all,

Have one that's driving me nuts:

I need to look at a column and find the last populated
cell of that column. Then I need to determine the first
blank row below that cell. My trouble is the next part...I
don't want to select this row or anything, I want to
assign a variable to it for use in another sub.

Hope I explained this clearly

Todd
 
F

Frank Kabel

Hi
try the following
Hi
try the following macro:
Sub find_last_row()
Dim lastcell As range
dim nextcell as range

set lastcell = ActiveSheet.Cells(Rows.Count, "Z").End(xlUp)
set nextcell = lastcell.offset(1,0)
nextcell.value="New cell"
End Sub
 
P

Pet e McCosh

Todd,

Dim LastRow as Integer
LastRow = Range("A65536").End(xlUp).Row

Will give you the row index of the last populated cell in
column A. You can then manipulate this number to your
heart's content.

Pete.
 
A

Alan Beban

Frank said:
Hi
try the following
Hi
try the following macro:
Sub find_last_row()
Dim lastcell As range
dim nextcell as range

set lastcell = ActiveSheet.Cells(Rows.Count, "Z").End(xlUp)
set nextcell = lastcell.offset(1,0)
nextcell.value="New cell"
End Sub
Click to expand...

The above code assigns to the variable "nextcell" the next cell after
the last occupied cell in Column Z whether or not the row of that cell
is blank. To do what the OP seems to be requesting, i.e., to assign to a
variable the next blank *row* (not the next blank cell), you might try:

Sub a()
Dim rng As Range, iCell As Range, myVar As Range
Set rng = Range(Range("Z65536").End(xlUp)(2), Range("Z65536"))
For Each iCell In rng
If Application.CountA(iCell.EntireRow) = 0 Then
Set myVar = iCell.EntireRow
Exit For
End If
Next
End Sub

Alan Beban
 
Top