Assigning a row to a variable
 

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

Assigning a row to a variable
 

dim myvariable as row

set myvariable = row("1:1")
myvariable.select





"Todd" wrote in message
...
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

Assigning a row to a variable
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


Todd wrote:
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

Assigning a row to a variable
 

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.

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


.

Assigning a row to a variable
 

Frank Kabel wrote:

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


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