|
blank rows
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? |
blank rows
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? |
blank rows
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? |
blank rows
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? |
blank rows
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? |
blank rows
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? |
blank rows
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? |
blank rows
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? |
| All times are GMT +1. The time now is 09:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com