MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selecting non-blank cells in several columns via VBA


Posted by Olivier on February 09, 2002 10:27 AM

Hello

This sub below will select all the cells in column A

Sub Select1colum()
Dim col As Range
Set col = Cells(Rows.Count, "A").End(xlUp)
Range("A1", col).Select
End Sub

But how do you select non-blank cells in several columns

Thanks


Posted by Guthrum on February 09, 2002 11:23 AM

It depends upon exactly what you want to select.
Here are a few examples that select cells in columns A & B.
On a blank sheet, enter something in cells A2,B6, & C10 then run each of the macros to see what they do.

Sub SelectColumns1()
Dim col As Range
Set col = Cells(Rows.Count, "A").End(xlUp)
Range("A1", col).Resize(, 2).Select
End Sub

Sub SelectColumns2()
Range(Range("A1"), Intersect(Range("A:B"), ActiveSheet.UsedRange)).Select
End Sub

Sub SelectColumns3()
Intersect(Range("A:B"), ActiveSheet.UsedRange).Select
End Sub

Sub SelectColumns4()
Dim LastCell As Range
Set LastCell = Range("A:B").Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows)
Range(Range("A1"), LastCell).Select
End Sub

Sub SelectColumns5()
Dim col1 As Range, col2 As Range
Set col1 = Cells(Rows.Count, "A").End(xlUp)
Set col2 = Cells(Rows.Count, "B").End(xlUp)
Union(Range("A1", col1), Range("B1", col2)).Select
End Sub


Posted by Olivier on February 09, 2002 11:45 AM

Thanks a lot GUTHRUM and thanks for the different options