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

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

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





.

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





.

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





.



.