I'm sure you could find lots of things about individual pieces (countblank and
areas, for example).

But to find the combination of things sometimes becomes more difficult--there's
probably lots, but to filter through all the hits could take awhile.

Nile Gilmanov wrote:

hah thank you Dave, now i am completely happy! :)))

All the best,
Nile
PS I wonder why I could not find anything like this on the usenet archives,
seems very practical to me :).

"Dave Peterson" wrote in message
...
You're correct about =countblank(). (I didn't try it.)

But you could either just use worksheetfunction.countblank() twice -- once
for
each set of columns or you could loop through each area (nicer if you ever
expand those columns to more non-contiguous areas):

Option Explicit
Sub testme()

Dim myRng As Range
Dim i As Long
Dim myRng2 As Range
Dim myBlankCtr As Long
Dim myArea As Range

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"))
myBlankCtr = 0
For Each myArea In myRng2.Areas
myBlankCtr = myBlankCtr +
WorksheetFunction.CountBlank(myArea)
Next myArea
If myBlankCtr = myRng2.Cells.Count Then
myRng(i).Delete
End If
Next i
End With
End Sub



Nile Gilmanov wrote:

Bah..... Dave I think that Worksheet function does not like intersected
rows, it returns the following error: Unable to get the CountBlank
property
of the WorksheetFunction class (Run-time error '1004':).. .and it does
work
with "RANGE", "RANGE" kind of notation.

It's too bad I was hoping it's all done :)))). Thanks anyway! :).

all the best,
Nile

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson