Selecting a Range

[Tuff]

Morning/afternoon everyone,

I am writing a macro that needs to highlight and copy a range of data. However the amount of rows used varies everytime it is run, and I don't know how to get it to just highlight the data that is there.

Similar to this except in the downward direction:
Selection.End(xlToRight).Select

(Basically I just want to the macro to do a Shift + Ctrl + down arrow).

 

[Arunner]

You can select the entire range (assuming there are no blank rows or columns in the range) as follows:

Range("A1&quot.currentregion.select
' change A1 to your top left hand cell in the range

This selects the range no matter what size it is.

 

[DaleWatson123321]

Tuff,

Morning to you too

There are various methods, but here's one that you'll be able to use.

Sub Transfer_Data()
Set_Data
GoTo_Destination
Copy_Data
End Sub

Sub Set_Data()
FirstCell = ActiveCell.Address
Lastcell = ActiveCell.End(xlDown).Offset(0, 4).Address
'Note: Change column offset above for number of columns
Data_Range = FirstCell & ":" & Lastcell
Range(Data_Range).Name = "data"
End Sub

Sub GoTo_Destination()
Worksheets("Destination&quot.Select
Lastcell = [A65536].End(xlUp).Offset(1, 0).Select
End Sub

Sub Copy_Data()
Range("data&quot.Copy
ActiveSheet.Paste
End Sub

Hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Bowers74]

To select the cells to the right of the active cell type:

ActiveCell.End(xlToRight).Select

To select the cells to the left of the active cell type:

ActiveCell.End(xlToLeft).Select

To select the cells above the active cell type:

ActiveCell.End(xlUp).Select

To select the cells below the active cell type What you wanted to do:

ActiveCell.End(xlDown).Select


Arunner -

Your suggestion is good, but I think that Tuff only wants to select the cells below his selection. If he has columns next to the active one then all of them will be selected.

Have a nice day!



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[Tuff]

Thanks guys all excellent suggestions.

But I think I will used to the
ActiveCell.End(x1Down).Select option.


If you can't work smart, then you should just work hard.