Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Deleting Rows using Macros
I need to format a sheet every month into a format that can be imported into
our accounting system - I plan to do this using macros. The sheet contains a number of rows that are not to be imported into the system and I wish to delete these - the rows to be deleted are those where column D contains no value. The spreadsheet has a different number of rows each month. Can anyone advise how this can best be done. Thanks in advance. |
#2
|
|||
|
|||
bernard,
Sub DeleteRows() Range("D:D").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End Sub This will only work if the cells are truly blank - not filled with a formula that returns "". HTH, Bernie MS Excel MVP "bernard" wrote in message ... I need to format a sheet every month into a format that can be imported into our accounting system - I plan to do this using macros. The sheet contains a number of rows that are not to be imported into the system and I wish to delete these - the rows to be deleted are those where column D contains no value. The spreadsheet has a different number of rows each month. Can anyone advise how this can best be done. Thanks in advance. |
#3
|
|||
|
|||
Excellent, worked a treat! Thanks again.
"Bernie Deitrick" wrote: bernard, Sub DeleteRows() Range("D:D").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End Sub This will only work if the cells are truly blank - not filled with a formula that returns "". HTH, Bernie MS Excel MVP "bernard" wrote in message ... I need to format a sheet every month into a format that can be imported into our accounting system - I plan to do this using macros. The sheet contains a number of rows that are not to be imported into the system and I wish to delete these - the rows to be deleted are those where column D contains no value. The spreadsheet has a different number of rows each month. Can anyone advise how this can best be done. Thanks in advance. |
#4
|
|||
|
|||
I like to add this
Use a on error in the code, this avoid a error if there are no blanks On Error Resume Next ' code On Error GoTo 0 Note: The limit of SpecialCells is 8192 separate areas -- Regards Ron de Bruin http://www.rondebruin.nl "bernard" wrote in message ... Excellent, worked a treat! Thanks again. "Bernie Deitrick" wrote: bernard, Sub DeleteRows() Range("D:D").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End Sub This will only work if the cells are truly blank - not filled with a formula that returns "". HTH, Bernie MS Excel MVP "bernard" wrote in message ... I need to format a sheet every month into a format that can be imported into our accounting system - I plan to do this using macros. The sheet contains a number of rows that are not to be imported into the system and I wish to delete these - the rows to be deleted are those where column D contains no value. The spreadsheet has a different number of rows each month. Can anyone advise how this can best be done. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicate rows then deleting them | Excel Worksheet Functions | |||
auto deleting defined rows | Excel Discussion (Misc queries) | |||
Converting formula to value and deleting rows using macros. | Excel Discussion (Misc queries) | |||
Deleting unwanted and empty rows.... | New Users to Excel | |||
Deleting Macros | Excel Worksheet Functions |