Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Columns I to Q range variable definition
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Columns I to Q range variable definition
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Columns I to Q range variable definition
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Columns I to Q range variable definition
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Columns I to Q range variable definition
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Columns I to Q range variable definition
Hi Claus,
Both test codes work well. Just exactly what I was looking for. Thanks much. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA variable definition help needed. | New Users to Excel | |||
Boolean Variable Definition | Excel Programming | |||
Excel question with variable definition | Excel Programming | |||
[variable] definition | Excel Programming | |||
variable range definition | Excel Programming |