cell clearance

  • Thread starter Thread starter martyn
  • Start date Start date
M

martyn

Hi, I got some excellent code last week from Tom Ogilvy
that
clears some cells if only the first couple are populated

ABCDEFGHIJKLMNOPQRSTU
XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXX
XXX
XXX
XXX
It works great, but what variable do i need to change if i
have a row like
ABCDEFGHIJKLMNOPQRSTU
XXXXXXX XXXXXX
because it is bringing up the information from the cells
below
ABCDEFGHIJKLMNOPQRSTU
XXXXXXXXXXXXXXXXXXXXX
XXXX^^^^^^^^^XXXXXXXX
XXXX XXXXXXXX
the code i use is:
Dim rng As Range, cell As Range
Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(0, 2)
For i = rng.Row To 1 Step -1
Set cell = Cells(i, 4)
cnt = 0
For Each cell1 In cell.Resize(1, 18)
If Len(Trim(cell1.Text)) = 0 Then
cnt = cnt + 1
End If
Next
If cnt = 18 Then
Cells(cell.Row, 1).EntireRow.Delete
End If
 
It is unclear what you are asking.

The code, as written deletes a row if the cells in columns D to U of that
row are empty.

you are now showing column D is filled, then there could be some blanks and
then information.

If you want to delete the row if columns E to M are blank

Dim rng As Range, cell As Range, clell1 as Range
Dim i as long, cnt as long
Set rng = Cells(Rows.Count, 1).End(xlUp)
For i = rng.Row To 1 Step -1
Set cell = Cells(i, 5)
cnt = 0
For Each cell1 In cell.Resize(1, 9)
If Len(Trim(cell1.Text)) = 0 Then
cnt = cnt + 1
End If
Next
If cnt = 9 Then
Cells(cell.Row, 1).EntireRow.Delete
End If

Another guess might be:
If you want to delete all rows after hitting the first row that has an empty
cell in say column E

Dim rng as Range, rng1 as Range, cell as Range
set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
for each cell in rng.offset(0,4)
if isempty(cell) then
set rng1 = cell
exit for
end if
Next
if not rng1 is nothing then
Range(rng1, Cells(rows.count,5)).EntireRow.Delete
End if
 
Back
Top