deleting rows with VB

[mantrid]

Hello

Can anyone tell ne the correct VB syntax loop through the rows in the active
worksheet and delete any row that has a cell in a given coulumn that does
not = null
I have the following

For Each c In ActiveSheet.Range("k3:k33").Cells
If c.Value Is Not Null Then
ActiveSheet.Row.Delete
End If

but it is not working
Any ideas please
Ian

 

[Norman Jones]

Hi Mantrid,

To delete rows, you shoulsd either proceed bottom to top or,
alternatively, delete the rows 'en masse'.

Try something like:

'================>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
Dim delRng As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set Rng = SH.Range("K3:K33") '<<===== CHANGE

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rCell In Rng.Cells
If IsEmpty(rCell.Value) Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<================

I have assumed that you wish to delete empty rows.

 

[mantrid]

Hi Mantrid,

To delete rows, you shoulsd either proceed bottom to top or,
alternatively, delete the rows 'en masse'.

Try something like:

'================>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
Dim delRng As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set Rng = SH.Range("K3:K33") '<<===== CHANGE

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rCell In Rng.Cells
If IsEmpty(rCell.Value) Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<================

I have assumed that you wish to delete empty rows.

Thanks Norman that has given me something to try. It looks like what I need
I will give it a go.

I have assumed that you wish to delete empty rows.

Well rows where any cell in the range K3:K33 is empty, but I think your code
is doing this? correct me if this is not right.

Ian

 

[Norman Jones]

Hi Ian

'-------------
Well rows where any cell in the range K3:K33 is empty, but I think your code
is doing this? correct me if this is not right.

'-------------

Yes.

 

[mantrid]

Hi Ian

'-------------
Well rows where any cell in the range K3:K33 is empty, but I think your code
is doing this? correct me if this is not right.

'-------------

Yes.

Ive looked at your code and it worked fine. I now need to change it a bit as
the rows I want to delete are NOT a definate range as in my original
posting. but can be any number. I thought the best way to takle this is to
use a do .... while .... loop incorporating a counter starting from 8 to
represent the first row looping until a number refering to a cell in column
A where it has nothing in it. This being the last row to examine for the
previous criteria we were looking for (ie a blank in column K). I have
attached my code below which is not working. Perhaps you would be kind
enough to have a look and see if you can see where I have gone wrong. Again
I relly appreciate your assistance

Thanks again
Ian
********************************************

Set WB = ActiveWorkbook
Set SH = WB.Sheets(newname)

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

counter = 8

Do While Not IsEmpty(SH.Cells(counter, 1).Value)

If Not IsEmpty(SH.Cells(counter, 11).Value) Then
If delRng Is Nothing Then
Set delRng = SH.Cells(counter, 11)
Else
Set delRng = Union(rCell, delRng)
End If
End If

counter = counter + 1
Loop

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

 

[Norman Jones]

Hi Mantrid,

My code was designed to delete all rows without a
value in the range K3:K33.

I regret that you statement:

Ive looked at your code and it worked fine. I now need to change it a bit
as
the rows I want to delete are NOT a definate range as in my original
posting. but can be any number

is unclear and indicate no specific criteria.

Try explaining the range of interest and the criteria which
should govern the proposed deletion of rows.