returning max UsedRange row number

  • Thread starter Thread starter drabbacs
  • Start date Start date
D

drabbacs

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
 
Range("A2:A", Range("B2").End(xldown).row) _
SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=R[-1]C"
 
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

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
Click to expand...


.
Click to expand...
 
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 said:
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

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
Click to expand...


.
Click to expand...
Click to expand...
 
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


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" <[email protected]> wrote
Click to expand...
in
message
I have created a macro which fills in the blank
Click to expand...
entries
of
columnA much like using f5->special->blanks uparrow & ctrl-
enter.

The code I have works. But I did it by cheating a
Click to expand...
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
Click to expand...
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





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top