cell clearance
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 |
cell clearance
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 -- Regards, Tom Ogilvy "martyn" wrote in message ... 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 |
All times are GMT +1. The time now is 06:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com