Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofill variable lenght column w/ sequential numbers using VBA
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofill variable lenght column w/ sequential numbers using VBA
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" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofill variable lenght column w/ sequential numbers using VB
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 mo (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 "Shane Devenshire" wrote: 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" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill a column with sequential numbers | Excel Discussion (Misc queries) | |||
Fill a column with sequential numbers | Excel Discussion (Misc queries) | |||
String parsing with variable lenght strings | Excel Worksheet Functions | |||
how do I autofil a column with sequential numbers? | Excel Discussion (Misc queries) | |||
AutoFill with Non-sequential Cell References ? | Excel Worksheet Functions |