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

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
 
K

Klaus

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
 
D

desmondleow

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
 
P

Patrick Molloy

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
 
T

Tom Ogilvy

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
 
D

desmondleow

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!
 
T

Tom Ogilvy

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
 
D

desmondleow

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!
 
D

Dave Ramage

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
 
T

Tom Ogilvy

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.
 
T

Tom Ogilvy

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.
 
Top