end of row? in a macro

[puppawinnie]

I am using Excel 97 and I have written a macro will be applied to a number of spreadsheets with same formats but varying column lengths. Is there a bit of code that XL 97 will recognize as the end of a column that I can replace in my Macro(eg instead of Range (A2:A89)(for argument sake) so that each worksheet will see this as the end of column and I won't have to manually change that line for each spreasheet?

 

[phantom1975]

Here is what you need:

Range("A65536").End(xlUp).Row

This will give you the last cell in Column A that has data.

 

[bobm]

Just to clarify Phantom's suggestion,

Range("A65536").End(xlUp).Row returns the row number of the last cell with data.

Range("A65536").End (xlUp) is the range of the last cell with data.

Bob

 

[Nimrod]

A little VBA demo for you :

Public Sub LastRowDemo()
LastRow = Range("A65536").End(xlUp).Row
NextEmptyRow = Range("A65536").End(xlUp).Row + 1

NL = Chr(13) & Chr(10)
ln1 = " In colA the Last Row Used is " & LastRow & NL
ln2 = " In colA the Last Row UnUsed is " & NextEmptyRow & NL
ln3 = " I will now select the used range for you..starting at A2" & NL
Title = "Last Row Demo"

pt = MsgBox(ln1 & ln2 & ln3, vbInformation, Title)

Range("A2:A" & LastRow).Select
End Sub

 

[puppawinnie]

Is the line "Range("A65536").End(xlUp).Row" going to replace "89" verbatim? This is what Bob is suggeting!?

 

[Nimrod]

PUP:

Look at the demo i've provided for you..

LastRow = Range("A65536").End(xlUp).Row
Range("A2:A" & LastRow).Select

Range("A65536").End(xlUp).Row will give you the row number

_________________
<font size=-1>NOTE:</font.<font size=-1> (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)</font><font size=+1><font color="blue">Adieu,<font color="red">N<font color="blue">imrod</font
This message was edited by Nimrod on 2003-02-15 13:14

 

[bobm]

Here is what I was going to post in response to "PUP's" last question, but since it is pretty much identical to Nimrod's I decided not to.

Try this:
<pre>Dim lngLastRow As Long
lngLastRow = Range("A65536").End(xlUp).Row
Range ("A2:A" & lngLastRow)</pre>

 

[puppawinnie]

Here's the relevant line of code as originally written "Selection.AutoFill Destination:=Range("B2:B89"), Type:=xlFillDefault then I replaced 89 with Range("A65536").End(xlUp).Row. I tried parentheses, quotes but I am still getting a syntax error. Please pardon my ignorance.

 

[puppawinnie]

Here's the relevant line of code as originally written "Selection.AutoFill Destination:=Range("B2:B89"), Type:=xlFillDefault then I replaced 89 with Range("A65536").End(xlUp).Row. I tried parentheses, quotes but I am still getting a syntax error. Please pardon my ignorance.

 

[Nimrod]

LastRow = Range("A65536").End(xlUp).Row


Selection.AutoFill Destination:=Range("B2:" & LastRow & "), Type:=xlFillDefault "
This message was edited by Nimrod on 2003-02-15 13:39