Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning max UsedRange row number
I have created a macro which fills in the blank entries of
columnA much like using f5-special-blanks uparrow & ctrl- enter. The code I have works. But I did it by cheating a bit. My data will be variable size from instance to instance, so what I did was find the bottom of my data with ActiveCell.End(xlDown)in columnB and then looked for blanks in columnA and columnB. I know there will be no blanks in B so the result is the blanks in A will be filled in. My question is, how would I do it without the cheating? I want a range from A2 to the last row in A but I'm not sure how to reference that. Also, the UsedRange property may be inaccurate since the sheet is created by copying another sheet. Code snippet follows. Range("B2").Select Range("A2", ActiveCell.End(xlDown)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" Thanks in advance Drabbacs |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning max UsedRange row number
Range("A2:A", Range("B2").End(xldown).row) _
SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _ "=R[-1]C" -- Regards, Tom Ogilvy "drabbacs" wrote in message ... I have created a macro which fills in the blank entries of columnA much like using f5-special-blanks uparrow & ctrl- enter. The code I have works. But I did it by cheating a bit. My data will be variable size from instance to instance, so what I did was find the bottom of my data with ActiveCell.End(xlDown)in columnB and then looked for blanks in columnA and columnB. I know there will be no blanks in B so the result is the blanks in A will be filled in. My question is, how would I do it without the cheating? I want a range from A2 to the last row in A but I'm not sure how to reference that. Also, the UsedRange property may be inaccurate since the sheet is created by copying another sheet. Code snippet follows. Range("B2").Select Range("A2", ActiveCell.End(xlDown)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" Thanks in advance Drabbacs |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning max UsedRange row number
That isn't working. It's returning an expected end of
statement error after the first line. -----Original Message----- Range("A2:A", Range("B2").End(xldown).row) _ SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _ "=R[-1]C" -- Regards, Tom Ogilvy "drabbacs" wrote in message ... I have created a macro which fills in the blank entries of columnA much like using f5-special-blanks uparrow & ctrl- enter. The code I have works. But I did it by cheating a bit. My data will be variable size from instance to instance, so what I did was find the bottom of my data with ActiveCell.End(xlDown)in columnB and then looked for blanks in columnA and columnB. I know there will be no blanks in B so the result is the blanks in A will be filled in. My question is, how would I do it without the cheating? I want a range from A2 to the last row in A but I'm not sure how to reference that. Also, the UsedRange property may be inaccurate since the sheet is created by copying another sheet. Code snippet follows. Range("B2").Select Range("A2", ActiveCell.End(xlDown)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" Thanks in advance Drabbacs . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning max UsedRange row number
As usual, trying to knit together a solution out of posted code results in
typos: Sub TesterAAA() Range("A2:A" & Range("B2").End(xlDown).Row). _ SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _ "=R[-1]C" End Sub -- Regards, Tom Ogilvy "drabbacs" wrote in message ... That isn't working. It's returning an expected end of statement error after the first line. -----Original Message----- Range("A2:A", Range("B2").End(xldown).row) _ SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _ "=R[-1]C" -- Regards, Tom Ogilvy "drabbacs" wrote in message ... I have created a macro which fills in the blank entries of columnA much like using f5-special-blanks uparrow & ctrl- enter. The code I have works. But I did it by cheating a bit. My data will be variable size from instance to instance, so what I did was find the bottom of my data with ActiveCell.End(xlDown)in columnB and then looked for blanks in columnA and columnB. I know there will be no blanks in B so the result is the blanks in A will be filled in. My question is, how would I do it without the cheating? I want a range from A2 to the last row in A but I'm not sure how to reference that. Also, the UsedRange property may be inaccurate since the sheet is created by copying another sheet. Code snippet follows. Range("B2").Select Range("A2", ActiveCell.End(xlDown)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" Thanks in advance Drabbacs . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning max UsedRange row number
That worked. Thanks. I was trying to debug it myself and I
caught the period on the end of line one myself. I was missing the ampersand operator instead of the comma for the range definition. Thanks again Drabbacs -----Original Message----- As usual, trying to knit together a solution out of posted code results in typos: Sub TesterAAA() Range("A2:A" & Range("B2").End(xlDown).Row). _ SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _ "=R[-1]C" End Sub -- Regards, Tom Ogilvy "drabbacs" wrote in message ... That isn't working. It's returning an expected end of statement error after the first line. -----Original Message----- Range("A2:A", Range("B2").End(xldown).row) _ SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _ "=R[-1]C" -- Regards, Tom Ogilvy "drabbacs" wrote in message ... I have created a macro which fills in the blank entries of columnA much like using f5-special-blanks uparrow & ctrl- enter. The code I have works. But I did it by cheating a bit. My data will be variable size from instance to instance, so what I did was find the bottom of my data with ActiveCell.End(xlDown)in columnB and then looked for blanks in columnA and columnB. I know there will be no blanks in B so the result is the blanks in A will be filled in. My question is, how would I do it without the cheating? I want a range from A2 to the last row in A but I'm not sure how to reference that. Also, the UsedRange property may be inaccurate since the sheet is created by copying another sheet. Code snippet follows. Range("B2").Select Range("A2", ActiveCell.End(xlDown)).Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" Thanks in advance Drabbacs . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching and returning row number of a value | Excel Worksheet Functions | |||
UsedRange problem | Excel Programming | |||
finding last row number of UsedRange | Excel Programming | |||
UsedRange problem | Excel Programming | |||
Usedrange | Excel Programming |