how to run through a table an delete the empty cells?

  • Thread starter Thread starter desmondleow
  • Start date Start date
D

desmondleow

I need to write a macro to run through a list of values in column A an
B.

If column A is blank, then I have to delete cells A and
correspondingly.

Is it very difficult to do that
 
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
 
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
 
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
 
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
 
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!
 
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
 
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!
 
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
 
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.
 
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.
 
Back
Top