delete row if no data, non-contiguous range problem

[Nile Gilmanov]

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
-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Guest]

Hi,
It seems you have identified part of the problem yourself. It does not work
on multiple selections. The code you put in does not delete the right row
because i is started at 1 and the row starts at 8:

Range("A8:A58,D8:K58").Select

I think you need to Col A, then D, E F G H I J and K one at a time

Selection.Rows(i).EntireRow.Delete (i is not the ActiveRow, if there is an
activerow)

 

[Dave Peterson]

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]

Thanks!

I will use your ideas and see how I can get this figured out.

All the best,
Nile

news:[email protected]...

 

[Nile Gilmanov]

Hey Dave,

what if I used a COUNTIF worksheet function instead of COUNTA (something
like COUNTIF(RANGE,""), do you think it's a good idea?

Nile

 

[Nile Gilmanov]

And by the way... Any Ideas how I can ignore the cells that have formulas
returning "", I think both counta and countif consider tham as cells that
have values.

The rows that I must delete have references to some worksheet with values
which sometimes don't exist, in that case "" shows. Please help me.

Nile

 

[Nile Gilmanov]

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]

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]

Dave,

Thanks a lot man, you really saved me a lot of time )).

LOL it's ironical cuz yesterday night I was working on this code of mine and
it wasn't doing the right thing I did feel like it had a bug, it would
ignore a value in the range that I gave and delete the row, cuz there was no
value in the column that I was supposed to be skipping ))).

may God keep blessing you with knowledge and wisdom and understanding ,
Nile

 

[Nile Gilmanov]

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]

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

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

 

[Nile Gilmanov]

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]

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.

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 .