Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fin End of Used Range
Im playing around with the code below, to try to get cells A2:A to fill, but
I cant get it working. Now I have this: Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select Range("A" & Last).Select Range(Selection, Cells(1)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" Im trying to fill in all blanks, between A2 and the end of the used range, with the value in the cells above (basically Ctrl+g, Special, Blanks, =, up arrow, ctrl+Enter). I cant find the end of the used range. Can someone please offer some assistance? Alternatively, I was considering finding the cell address of the last used cell in Column A, and using that in a macro. Perhaps something like: 'J1 contains a function that finds the cell address of the last nonblank cell... Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select Range("A" & J1).Select Range(Selection, Cells(1)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" Any ideas on this? Cordially, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fin End of Used Range
Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Range("A1:A" & Last).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ryguy7272" wrote in message ... I'm playing around with the code below, to try to get cells A2:A to fill, but I can't get it working. Now I have this: Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select Range("A" & Last).Select Range(Selection, Cells(1)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" I'm trying to fill in all blanks, between A2 and the end of the used range, with the value in the cells above (basically Ctrl+g, Special, Blanks, =, up arrow, ctrl+Enter). I can't find the end of the used range. Can someone please offer some assistance? Alternatively, I was considering finding the cell address of the last used cell in Column A, and using that in a macro. Perhaps something like: 'J1 contains a function that finds the cell address of the last nonblank cell... Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select Range("A" & J1).Select Range(Selection, Cells(1)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" Any ideas on this? Cordially, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fin End of Used Range
Awesome!! Thanks for getting me over the hurdle Bob!
Cordially, Ryan--- -- RyGuy "Bob Phillips" wrote: Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Range("A1:A" & Last).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ryguy7272" wrote in message ... I'm playing around with the code below, to try to get cells A2:A to fill, but I can't get it working. Now I have this: Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select Range("A" & Last).Select Range(Selection, Cells(1)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" I'm trying to fill in all blanks, between A2 and the end of the used range, with the value in the cells above (basically Ctrl+g, Special, Blanks, =, up arrow, ctrl+Enter). I can't find the end of the used range. Can someone please offer some assistance? Alternatively, I was considering finding the cell address of the last used cell in Column A, and using that in a macro. Perhaps something like: 'J1 contains a function that finds the cell address of the last nonblank cell... Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select Range("A" & J1).Select Range(Selection, Cells(1)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" Any ideas on this? Cordially, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |