deleting rows with VB
 

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

deleting rows with VB
 

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.


---
Regards,
Norman



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

deleting rows with VB
 

"Norman Jones" wrote in message
...
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.


---
Regards,
Norman


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

deleting rows with VB
 

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.

---
Regards,
Norman