delete row if no data, non-contiguous range problem
Be careful. It looks to me like you have a small bug in your code.
This line:
Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K"))
isn't the same as:
Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K"))
==
(It's actually the same as:
Set myRng2 = Intersect(myRng(i), .Range("A:K"))
And I bet you wanted to avoid column C.)
=======
And just a personal preference:
Instead of:
If WorksheetFunction.CountBlank(myRng2) = 10 Then
I like:
If WorksheetFunction.CountBlank(myRng2) = myrng2.cells.count Then
if you're checking all the cells in that range. Then if you change the range,
you don't have to remember to change that 10.
Nile Gilmanov wrote:
AWESOME this works very well... xcept i had to modify it just a smidge!
I used COUNTBLANK function since it ignores any formulas that might be
returning "" in the cells. :)))).
With ActiveSheet
Set myRng = .Rows("8:58")
'working backwords because deleting rows.
For i = myRng.Rows.Count To 1 Step -1
Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K"))
If WorksheetFunction.CountBlank(myRng2) = 10 Then
myRng(i).Delete
Else
End If
Next i
End With
"Dave Peterson" wrote in message
...
I think I'd loop through the rows, but check each column that I wanted:
Option Explicit
Sub testme()
Dim i As Long
Dim myRng As Range
Dim myRng2 As Range
'turn off calculation and screenupdating.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
Set myRng = .Rows("8:58")
'working backwords because deleting rows.
For i = myRng.Rows.Count To 1 Step -1
Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k"))
If WorksheetFunction.CountA(myRng2) = 0 Then
myRng(i).Delete
End If
Next i
End With
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
I also got rid of the selection stuff.
And I could drop the .entirerow since I was dealing with the entire row to
start.
Nile Gilmanov wrote:
Hi my name is Nile.
I have the following code that checks every row from the bottom for any
data
and having found none deletes it, then goes on, it is limited to the
certain
range.
It works fine with contiguous range such as ("A10:C20"), but does not
work
with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone
help
me please?
here is the code:
-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-
Dim i As Long
'turn off calculation and screenupdating.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
Range("A8:A58,D8:K58").Select
'working backwords because deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-
--
Dave Peterson
--
Dave Peterson
|