Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Efficient data clearance in Excel spreadsheets | Excel Discussion (Misc queries) | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Creating a check box that does not require security clearance. | Excel Worksheet Functions |