How do you append new rows to the end of a workbook?

If I find the last row using ActiveCell.SpecialCells(xlLastCell).Row and
append my stuff, I often find blank rows above the newly appended ones
because somebody had entered data and then cleared it, which moves the
LastCell downwards.

I am trying to delete the blank rows by looping thru all rows to detect
blank ones, but the IsBlank function won't work in VBA. Are there
workarounds?

See http://www.contextures.com/xlfaqApp.html#Unused

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Phil H" <Phil wrote in message
...
How do you append new rows to the end of a workbook?

If I find the last row using ActiveCell.SpecialCells(xlLastCell).Row and
append my stuff, I often find blank rows above the newly appended ones
because somebody had entered data and then cleared it, which moves the
LastCell downwards.

I am trying to delete the blank rows by looping thru all rows to detect
blank ones, but the IsBlank function won't work in VBA. Are there
workarounds?

xlLastCell and Usedrange are both dependant on the state of the spreadsheet
when it was last saved... This makes both functions a little dangerous. You
are better off to use a function that moves up from the las cell in a column
sismlar to...

sheets("Sheet1").range("A65535").end(xlUp).offset( 1,0).select

This selects the first blank cell in Column A looking up from row 65,535.
You can change the sheet the the column and or the cell that you are looking
up from... So long as the bottom of the sheet is not "Ragged" then this works
great.

HTH

"Phil H" wrote:

How do you append new rows to the end of a workbook?

If I find the last row using ActiveCell.SpecialCells(xlLastCell).Row and
append my stuff, I often find blank rows above the newly appended ones
because somebody had entered data and then cleared it, which moves the
LastCell downwards.

I am trying to delete the blank rows by looping thru all rows to detect
blank ones, but the IsBlank function won't work in VBA. Are there
workarounds?

I like using the CurrentRegion for this, as it looks specifically for filled
cells. So Range("A1").CurrentRegion.Rows.Count tells me how many rows are
used in A; I can then insert the data into the next row. But this only works
(strictly speaking) for rectangular ranges of contiguous data.

"Phil H" wrote:

How do you append new rows to the end of a workbook?

If I find the last row using ActiveCell.SpecialCells(xlLastCell).Row and
append my stuff, I often find blank rows above the newly appended ones
because somebody had entered data and then cleared it, which moves the
LastCell downwards.

I am trying to delete the blank rows by looping thru all rows to detect
blank ones, but the IsBlank function won't work in VBA. Are there
workarounds?

For a real radical idea; Sort!


Regards
Robert McCurdy

"Phil H" <Phil wrote in message ...
How do you append new rows to the end of a workbook?

If I find the last row using ActiveCell.SpecialCells(xlLastCell).Row and
append my stuff, I often find blank rows above the newly appended ones
because somebody had entered data and then cleared it, which moves the
LastCell downwards.

I am trying to delete the blank rows by looping thru all rows to detect
blank ones, but the IsBlank function won't work in VBA. Are there
workarounds?

xlLastCell and Usedrange are both dependant on the state of the spreadsheet
when it was last saved.

that is only partially true - you could say as a minimum, because it might
be greater.

Also, it is possible to make it smaller using code, but you should know if
you did that.

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in message
...
xlLastCell and Usedrange are both dependant on the state of the
spreadsheet
when it was last saved... This makes both functions a little dangerous.
You
are better off to use a function that moves up from the las cell in a
column
sismlar to...

sheets("Sheet1").range("A65535").end(xlUp).offset( 1,0).select

This selects the first blank cell in Column A looking up from row 65,535.
You can change the sheet the the column and or the cell that you are
looking
up from... So long as the bottom of the sheet is not "Ragged" then this
works
great.

HTH

"Phil H" wrote:

How do you append new rows to the end of a workbook?

If I find the last row using ActiveCell.SpecialCells(xlLastCell).Row and
append my stuff, I often find blank rows above the newly appended ones
because somebody had entered data and then cleared it, which moves the
LastCell downwards.

I am trying to delete the blank rows by looping thru all rows to detect
blank ones, but the IsBlank function won't work in VBA. Are there
workarounds?

A good idea, altho not in my particular circumstances. Thanks!!

"EvolBob" wrote:

For a real radical idea; Sort!


Regards
Robert McCurdy

"Phil H" <Phil wrote in message ...
How do you append new rows to the end of a workbook?

If I find the last row using ActiveCell.SpecialCells(xlLastCell).Row and
append my stuff, I often find blank rows above the newly appended ones
because somebody had entered data and then cleared it, which moves the
LastCell downwards.

I am trying to delete the blank rows by looping thru all rows to detect
blank ones, but the IsBlank function won't work in VBA. Are there
workarounds?

Good idea, thanks!!

"K Dales" wrote:

I like using the CurrentRegion for this, as it looks specifically for filled
cells. So Range("A1").CurrentRegion.Rows.Count tells me how many rows are
used in A; I can then insert the data into the next row. But this only works
(strictly speaking) for rectangular ranges of contiguous data.

"Phil H" wrote:

How do you append new rows to the end of a workbook?

If I find the last row using ActiveCell.SpecialCells(xlLastCell).Row and
append my stuff, I often find blank rows above the newly appended ones
because somebody had entered data and then cleared it, which moves the
LastCell downwards.

I am trying to delete the blank rows by looping thru all rows to detect
blank ones, but the IsBlank function won't work in VBA. Are there
workarounds?

Jim, this works best for me. Thanks!!

"Jim Thomlinson" wrote:

xlLastCell and Usedrange are both dependant on the state of the spreadsheet
when it was last saved... This makes both functions a little dangerous. You
are better off to use a function that moves up from the las cell in a column
sismlar to...

sheets("Sheet1").range("A65535").end(xlUp).offset( 1,0).select

This selects the first blank cell in Column A looking up from row 65,535.
You can change the sheet the the column and or the cell that you are looking
up from... So long as the bottom of the sheet is not "Ragged" then this works
great.

HTH

"Phil H" wrote:

How do you append new rows to the end of a workbook?

If I find the last row using ActiveCell.SpecialCells(xlLastCell).Row and
append my stuff, I often find blank rows above the newly appended ones
because somebody had entered data and then cleared it, which moves the
LastCell downwards.

I am trying to delete the blank rows by looping thru all rows to detect
blank ones, but the IsBlank function won't work in VBA. Are there
workarounds?