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.

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/

.

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

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/

.

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/

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/

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/

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/

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/

.

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/

.

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/

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/

.