Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |