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

[David]

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)


"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]

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

[Nile Gilmanov]

Thanks!

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

All the best,
Nile

"Nile Gilmanov" <[email protected]> wrote in message
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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

[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


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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