area selection in VB

[tonlien]

Hi,
I recorded a macro which includes the selection of a group of cells using the key strokes "end" + "arrow down".
This macro has to work an various input files with different length of columns so, I thought the "end" + "arrow down" would solve that as it works fine when I manually use these keystrokes. However, when I replay the macro, the selection of cells is identical to that when recording the macro, independent of how many cells are actually in the column in the worksheet in use.

How can I, using a macro, select all non blank cells in a column. The macro has to work for worksheets with different length of columns (no. of filled cells) ?

Thanks,
Ton

 

[jindon]

One way
Balnk is Balnk, Not ""
try

Sub test()
Dim rng As Range
On Error Resume Next
With Range("a1", Range("a" & Rows.Count).End(xlUp))
     Set rng = .SpecialCells(2,23)
     Union(rng, .SpecialCells(-4123,23)).Select
End With
End Sub

 

[tonlien]

Nothing happens

Thanks for your suggestion.
I have to tell you I know absolutely nothing about VB.
I just record key strokes in Excel and the VB is created automatically.

Now I used "Alt"+F8 then typed a new name in the box "ColumnSelect" which resulted in turning the greyed out "Create" button into black and I selected it. It opened the VB editor and I pasted your code in the box.

Sub test() [changed this into "Sub ColumnSelect"
Dim rng As Range
On Error Resume Next
With Range("a1", Range("a" & Rows.Count).End(xlUp))
Set rng = .SpecialCells(2,23)
Union(rng, .SpecialCells(-4123,23)).Select
End With
End Sub

Then I saved and closed.
Then I positioned the cursor at A1, the top of a column.
Then I used "Alt"+F8 again, selected the macro and hit "Run".
Then nothing happened.

What did I do wrong?

Nothing in my sheet