How do I select this range using code....another try at it.
 

I have four columns that represent the various parts of a road name. The
respective headers are, in order, Prefix, RdName, Suffix and PostDir.

The headers occupy cells A1 through D1.

A user will copy relevant data into this sheet, with cell A2 being the first
cell that could potentially contain data. Most often cell A2 will NOT
contain a value.

It is likely that one of the four columns will contain no data at all
(except for the header).

It is equally likely that there will be sections where there is no data at
all for several rows in a row.

What I need to do:

I need to be able to select the range from cell A2 to E? where the ?
represents the last row number that contains a value.

John Wilson and Per Erik both offered advice in a previous post, but neither
method worked.

TIA.

-gk-

How do I select this range using code....another try at it.
 

Maybe this will help ... though by starting a new thread we don't get to see
what you've already been offered as potential solutions.

Sub LastRow_AtoD()
Dim LastRow As Long
LastRow = Application.WorksheetFunction.Max _
(Range("A65536").End(xlUp).Row, _
Range("B65536").End(xlUp).Row, _
Range("C65536").End(xlUp).Row, _
Range("D65536").End(xlUp).Row)
Range("A1:E" & LastRow).Select
MsgBox LastRow
End Sub

Regards

Trevor


"TBA" wrote in message
...
I have four columns that represent the various parts of a road name. The
respective headers are, in order, Prefix, RdName, Suffix and PostDir.

The headers occupy cells A1 through D1.

A user will copy relevant data into this sheet, with cell A2 being the
first
cell that could potentially contain data. Most often cell A2 will NOT
contain a value.

It is likely that one of the four columns will contain no data at all
(except for the header).

It is equally likely that there will be sections where there is no data at
all for several rows in a row.

What I need to do:

I need to be able to select the range from cell A2 to E? where the ?
represents the last row number that contains a value.

John Wilson and Per Erik both offered advice in a previous post, but
neither
method worked.

TIA.

-gk-

How do I select this range using code....another try at it.
 

The only way i know of to do it and be absolutely sure that it i
correct



Sub Macro1()
Dim i As Integer
Dim r As Long
Dim t As Long
For i = 1 To 5 Step 1
t = Cells(65536, i).End(xlUp).Row
If t r Then
r = t
End If
Next i
If r 1 Then
Range("a2:e" & r).Select
End If
End Sub


you could also use

If rows have nbeen deleted from the bottom of you sheet this wil
include those deleted rows in the row so you could end up with severa
blank rows at the bottom selected

sub Macro2
dim r as long
r=Range("a1").SpecialCells(xlLastCell).Row
range("a2:e" & r).select)
end sub

If rows have nbeen deleted from the bottom of you sheet this wil
include those deleted rows in the row so you could end up with severa
blank rows at the bottom selecte

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