What's a better way to capture the range of Columns I:Q down to last row the with an entry.

I don't know which column will have the longest list of data.

I use select here just to verify the range on the sheet, I will set the range to a range object when I find the better way.

I understand xlCellTypeLastCell is only reduced by deleting the cells. The range may be 1400 rows or only 11 as an example.

When it is only 11, I don't want my Find code to have to search all the wasted unused range.

Thanks,
Howard


Sub aMethod()
Dim WS As Worksheet
Dim LastCell As Range
Dim lcCel As Long

Set LastCell = Range("I:Q").SpecialCells(xlCellTypeLastCell)
lcCel = LastCell.Row
Range(Cells(3, 9), Cells(lcCel, 17)).Select
End Sub

Hi Howard,

Am Fri, 26 Feb 2016 03:41:50 -0800 (PST) schrieb L. Howard:

What's a better way to capture the range of Columns I:Q down to last row the with an entry.

I don't know which column will have the longest list of data.

I use select here just to verify the range on the sheet, I will set the range to a range object when I find the better way.

I understand xlCellTypeLastCell is only reduced by deleting the cells. The range may be 1400 rows or only 11 as an example.

xlCellTypeLastCell is not reliable. If you have formats downwards the
existing data you will get a wrong range.
I would check the last cell of I and J and use the maximum:

LRow1 = Cells(Rows.Count, "I").End(xlUp).Row
LRow2 = Cells(Rows.Count, "J").End(xlUp).Row

Set myRng = Range("I1:J" & Application.Max(LRow1, LRow2))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

xlCellTypeLastCell is not reliable. If you have formats downwards the
existing data you will get a wrong range.
I would check the last cell of I and J and use the maximum:

LRow1 = Cells(Rows.Count, "I").End(xlUp).Row
LRow2 = Cells(Rows.Count, "J").End(xlUp).Row

Set myRng = Range("I1:J" & Application.Max(LRow1, LRow2))


Regards
Claus B.
--

Hi Claus,

I assume "J" is a typo, I need

myRng = Range("I1:Q" & Application.Max(LRow1, LRow2))

However, if any column between "I" and "Q" has more rows than "I" or "Q" then there will be some data not included in myRng.

I need something like this,

RNofLongCol = (Longest Column from I to Q).Row

Set myRng = Range("I1:Q" & RNofLongCol))

Howard

Hi Howard,

Am Fri, 26 Feb 2016 08:17:58 -0800 (PST) schrieb L. Howard:

I assume "J" is a typo, I need

myRng = Range("I1:Q" & Application.Max(LRow1, LRow2))

sorry, my bad. I didn't wear my glasses.
Try:

Sub Test()
Dim varRows(8) As Variant
Dim i As Long
Dim myRng As Range

For i = Columns("I").Column To Columns("Q").Column
varRows(i - 9) = Cells(Rows.Count, i).End(xlUp).Row
Next
Set myRng = Range("I1:Q" & Application.Max(varRows))
'myRng.Select
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Fri, 26 Feb 2016 17:25:57 +0100 schrieb Claus Busch:

Sub Test()
Dim varRows(8) As Variant

or if you don't want to count the columns first:

Sub Test()
Dim varRows() As Variant
Dim i As Long, myFirst As Long, myLast As Long
Dim myRng As Range

myFirst = Columns("I").Column
myLast = Columns("Q").Column
ReDim Preserve varRows(myLast - myFirst)

For i = myFirst To myLast
varRows(i - myFirst) = Cells(Rows.Count, i).End(xlUp).Row
Next
Set myRng = Range("I1:Q" & Application.Max(varRows))
'myRng.Select
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

Both test codes work well.

Just exactly what I was looking for.

Thanks much.

Howard