area selection in VB

tonlien

New Member
Joined
Jul 22, 2007
Messages
2
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
One way
Balnk is Balnk, Not ""
try
Code:
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

New Member
Joined
Jul 22, 2007
Messages
2
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
 

Forum statistics

Threads
1,181,055
Messages
5,927,862
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top