Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
I need to write a macro to run through a list of values in column A an
B. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
Try the following macro:
Sub DelteValues() x = 25 'for example For i = 1 To x If Cells(i, 1).Value = "" Then Cells(i, 2).Value = "" ' or to delete the entire row: ' Rows(i).EntireRow.Delete End If Next End Sub Regards Klaus -----Original Message----- I need to write a macro to run through a list of values in column A and B. If column A is blank, then I have to delete cells A and B correspondingly. Is it very difficult to do that? --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
Thanks Klaus!
Is there any way to just delete cells from A:B and shift it up to clos up the gap? I cannot delete the whole row because I have data fro columns C onwards. Would appreciate your assistance! Thanks -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
Klaus shows how to clear a cell in column B if the cell
in column A is blank. The question asks how to "delete" A & B Sub DeleteRows() Dim Target As Range, thisrow As Range Dim cell As Range Set Target = _ ThisWorkbook.Names("MyTable").RefersToRange For Each thisrow In Target.Rows If thisrow.Range("A1") = "" Then thisrow.Delete End If Next End Sub This will remove all cells in a table row - just pass the table name or set the Target range to whatever range you want Patrick Molloy Microsoft Excel MVP -----Original Message----- I need to write a macro to run through a list of values in column A and B. If column A is blank, then I have to delete cells A and B correspondingly. Is it very difficult to do that? --- Message posted from http://www.ExcelForum.com/ . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
Note that you need to loop from the highest row to the lowest row:
Sub DelteValues() x = 25 'for example For i = x To 1 Step -1 If Cells(i, 1).Value = "" Then Cells(i, 1).Resize(1, 2).Delete Shift:=xlShiftUp End If Next End Sub -- Regards, Tom Ogilvy "desmondleow" wrote in message ... Thanks Klaus! Is there any way to just delete cells from A:B and shift it up to close up the gap? I cannot delete the whole row because I have data from columns C onwards. Would appreciate your assistance! Thanks! --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
Thanks Klaus!
Is there any way to just delete cells from A:B and shift it up to close up the gap? I cannot delete the whole row because I have data from columns C onwards. Would appreciate your assistance! Thanks! --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
Note that you need to loop from the highest row to the lowest row:
Sub DelteValues() x = 25 'for example For i = x To 1 Step -1 If Cells(i, 1).Value = "" Then Cells(i, 1).Resize(1, 2).Delete Shift:=xlShiftUp End If Next End Sub -- Regards, Tom Ogilvy "desmondleow" wrote in message ... Thanks Klaus! Is there any way to just delete cells from A:B and shift it up to close up the gap? I cannot delete the whole row because I have data from columns C onwards. Would appreciate your assistance! Thanks! --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
Hi Patrick!
Thanks for your tip! I was trying out this code here but it doesn't work properly: With Worksheets("SGTemp") intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row For intRow = 1 To intLastRow If IsEmpty(.Cells(intRow, 1)) Then Range("A" + Format(intRow) + ":B" + Format(intRow)).Select Selection.Delete Shift:=xlUp 'ActiveCell.Range("A" + Format(intRow) + ":B" + Format(intRow)).Delete End If Next intRow End With In the above code, when I have two or more consecutive blank rows, it skips the following blank row instead. Is there a way to make the code delete the blank cells A & B when it finds one? Thanks for your assistance! --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
This should be about the quickest way, as long as it is
acceptable to delete the entire row. Select the cells in column A then run it. Sub Test() Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete End Sub Cheers, Dave -----Original Message----- I need to write a macro to run through a list of values in column A and B. If column A is blank, then I have to delete cells A and B correspondingly. Is it very difficult to do that? --- Message posted from http://www.ExcelForum.com/ . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
This will skip rows if there are two sequential rows that need to be
deleted. The usual solution is to loop from highest row to lowest row. -- Regards, Tom Ogilvy "Patrick Molloy" wrote in message ... Klaus shows how to clear a cell in column B if the cell in column A is blank. The question asks how to "delete" A & B Sub DeleteRows() Dim Target As Range, thisrow As Range Dim cell As Range Set Target = _ ThisWorkbook.Names("MyTable").RefersToRange For Each thisrow In Target.Rows If thisrow.Range("A1") = "" Then thisrow.Delete End If Next End Sub This will remove all cells in a table row - just pass the table name or set the Target range to whatever range you want Patrick Molloy Microsoft Excel MVP -----Original Message----- I need to write a macro to run through a list of values in column A and B. If column A is blank, then I have to delete cells A and B correspondingly. Is it very difficult to do that? --- Message posted from http://www.ExcelForum.com/ . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
See my two responses for code that will work.
-- Regards, Tom Ogilvy "desmondleow" wrote in message ... Hi Patrick! Thanks for your tip! I was trying out this code here but it doesn't work properly: With Worksheets("SGTemp") intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row For intRow = 1 To intLastRow If IsEmpty(.Cells(intRow, 1)) Then Range("A" + Format(intRow) + ":B" + Format(intRow)).Select Selection.Delete Shift:=xlUp 'ActiveCell.Range("A" + Format(intRow) + ":B" + Format(intRow)).Delete End If Next intRow End With In the above code, when I have two or more consecutive blank rows, it skips the following blank row instead. Is there a way to make the code delete the blank cells A & B when it finds one? Thanks for your assistance! --- Message posted from http://www.ExcelForum.com/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to run through a table an delete the empty cells?
if not, and as stated it isn't, then you can use this
Sub Test() Dim rng As Range Set rng = Columns(1).SpecialCells(xlCellTypeBlanks) Set rng = Intersect(rng.EntireRow, Range("A:B")) rng.Delete shift:=xlShiftUp End Sub to just delete the cells in columns A and B where the cell in A is blank. -- Regards, Tom Ogilvy "Dave Ramage" wrote in message ... This should be about the quickest way, as long as it is acceptable to delete the entire row. Select the cells in column A then run it. Sub Test() Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete End Sub Cheers, Dave -----Original Message----- I need to write a macro to run through a list of values in column A and B. If column A is blank, then I have to delete cells A and B correspondingly. Is it very difficult to do that? --- Message posted from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete row if 2 cells are empty | Excel Discussion (Misc queries) | |||
delete cells column. Delete empty cells | Excel Worksheet Functions | |||
How to delete at once empty columns in a table? | Excel Discussion (Misc queries) | |||
How do I delete rows where cells are empty | Excel Discussion (Misc queries) | |||
delete rows with empty cells | Excel Discussion (Misc queries) |