Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row if column K is blank
Hi. Im new to VB in Excel! Man its real different to pure VB! What I want to do is cycle trough a spreadsheet and check column k fo a blank space (i.e "") If it is blank space, I want to delete the entire row. Im not sure how I move through the entire spreadsheet either. Obviously when doing it in Access you can use: Do while not spreadsheet.EOF Can someone help me ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row if column K is blank
Sub DeleteBlankRows()
Dim Target As Range Set Target = _ Sheet1.Range("K:K").SpecialCells(xlCellTypeBlanks) Target.EntireRow.Delete End Sub Instead of K:K you could use a more defined range Patrick Molloy Microsft Excel MVP -----Original Message----- Hi. Im new to VB in Excel! Man its real different to pure VB! What I want to do is cycle trough a spreadsheet and check column k for a blank space (i.e "") If it is blank space, I want to delete the entire row. Im not sure how I move through the entire spreadsheet either. Obviously when doing it in Access you can use: Do while not spreadsheet.EOF Can someone help me? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row if column K is blank
try this
Sub noblanks() Range("k1:k17").SpecialCells(xlBlanks).Delete End Sub -- Don Guillett SalesAid Software "andycharger" wrote in message ... Hi. Im new to VB in Excel! Man its real different to pure VB! What I want to do is cycle trough a spreadsheet and check column k for a blank space (i.e "") If it is blank space, I want to delete the entire row. Im not sure how I move through the entire spreadsheet either. Obviously when doing it in Access you can use: Do while not spreadsheet.EOF Can someone help me? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row if column K is blank
Just to add that if there are no blank cells in Column K, you will get an
error with specialcells. It is usually advisable to use error trapping when using specialcells Sub DeleteBlankRows() Dim Target As Range On Error Resume Next Set Target = _ Sheet1.Range("K:K").SpecialCells(xlCellTypeBlanks) On Error goto 0 if not Target is nothing then _ Target.EntireRow.Delete End Sub Excel has a variable that describes the used region of the worksheet. Activesheet.UsedRange This defines the rectangular area of cells that Excel considers to be used and which it stores information about. (the remainder of the cells are essentually "virtual"). This will always include cells containing data, but may include additional cells that have a custom format applied - and so this area may overstate what you would consider to be actually in use. For data that is organized like a data base, you can refer to it with currentregion. Range("A1").CurrentRegion if you want to loop through cells in either you can do Dim cell as Range for each cell in Range("A1").CurrentRegion or For each cell in Activesheet.UsedRange as examples. -- Regards, Tom Ogilvy "Patrick Molloy" wrote in message ... Sub DeleteBlankRows() Dim Target As Range Set Target = _ Sheet1.Range("K:K").SpecialCells(xlCellTypeBlanks) Target.EntireRow.Delete End Sub Instead of K:K you could use a more defined range Patrick Molloy Microsft Excel MVP -----Original Message----- Hi. Im new to VB in Excel! Man its real different to pure VB! What I want to do is cycle trough a spreadsheet and check column k for a blank space (i.e "") If it is blank space, I want to delete the entire row. Im not sure how I move through the entire spreadsheet either. Obviously when doing it in Access you can use: Do while not spreadsheet.EOF Can someone help me? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting blank rows | New Users to Excel | |||
deleting rows with blank cells after a specified column? | Excel Discussion (Misc queries) | |||
Deleting all but one blank row | Excel Discussion (Misc queries) | |||
Deleting blank modules | Excel Discussion (Misc queries) | |||
Deleting blank cells in a column | Excel Discussion (Misc queries) |