autofill variable lenght column w/ sequential numbers using VBA

[Billyruben]

Problem: Sort data contained in an area defined by X number of columns and Y
number of rows, then sort the data back to its orginal row order.

Normally: Before the initial sort, add an extra column and assign a unique
number to each row. Sort using this extra column will restore the original
row order.

I can record a macro and display the VBA code without a problem.

Question: Write the code identifying the last sort row when # of rows not
constant. eg: Selection.AutoFill Destination:=Range("F5:F??"),
Type:=xlFillDefault. I can count the # of rows, but how do I store that
number, then pass it to the Range requirements?

 

[Shane Devenshire]

Hi

Dim Bottom as Long
Bottom = Range("A65536").End(XLUp).Row
Selection.AutoFill Destination:=Range("F5:F" & Bottom)

I picked colum A to find the bottom of the data, but you can pick any column
where the data extends down as far as you want the fill.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Billyruben]

Excellent! Thanks, I've used the value stored in Bottom for accomplishing
other things. (e.g. discovered that Range("A" & Bottom + n).Select, selects
the cell located n number of rows below the value of Bottom)

Please allow me to impose on you once more:

(I know zilch about VBA syntax.)

What is the VBA equivelant of CtrlShiftSpacebar - e.g. select an area of
contiguous cells, each containing data. Start by selecting the topmost cell
containing data at the leftmost column.

What is a good source for the code equivalents for keyboard shortcuts?
(Other than you, of course)

Thanks for being out there.
Ruben