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

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" <(E-Mail Removed)> wrote in message
news:OAQUh.6126$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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

"Norman Jones" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>

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

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.


---
Regards,
Norman


"mantrid" <(E-Mail Removed)> wrote in message
news:gXvVh.3523$(E-Mail Removed)...
>
> "Norman Jones" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>
> 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
>
>
>
>