VBA - how do i find and locate certain rows to delete in excel?

[HP1701]

(I'm new to VBA but keen to learn, so please help and educate me!)

So, mid spreadsheet, done some sorting of data in ascending value order. Now I have a block of rows in the middle where the final calculation produces a zero (i have both column Y with this in as formulae, and column AD where it's pasted as values).

I now want to locate and delete these rows, so the remaining rows above (being negative answers), and those below (being positive answers) end up sitting next to each other.

How can I do this?

Thanks

HP

 

[maxwell_r_ryan]

Hi, not sure if you were looking for something like this?


Sub DeleteRows()

Dim i As Long


With Application

.Calculation = xlCalculationManual

.ScreenUpdating = False



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

End sub



Cheers,
Max

 

[jim may]

Why not try Recording a Macro to perform. Include in your Macro Planning the use of an auto-filter, then selecting your Column Y selecting the 0's, then Deleting all Filtered Rows, then Unfilter, Stop Macro Recorder.

 

[nkarthick]

Hi,

Try the below code, hope it will solve your problem.

Instead of Sheet1 in the code use your respective sheet (code) name.

Sub DeleteRowsWithZeroValues()
    Dim rng As Excel.Range
    Dim lastOccurenceRow As Long
    Dim firstOccurenceRow As Long
    
    'Get the First Occurence of zero value in the AD Column in Sheet1
    Set rng = Sheet1.Range("AD:AD").Find(what:="0", Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
    If Not rng Is Nothing Then
    
        firstOccurenceRow = rng.Row
        
        'Get the Last occurence of ) value in AD column in Sheet1
        Set rng = Sheet1.Range("AD:AD").Find(what:="0", Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlPrevious)
        lastOccurenceRow = rng.Row
        
        Dim i As Integer
        
        For i = lastOccurenceRow To firstOccurenceRow Step -1
        
            If (Sheet1.Range("AD" & i).Value = "0") Then
                'Delete the row ith zero value
                Sheet1.Rows(i).EntireRow.Delete
            End If
            
        Next i
        
    End If

End Sub

Thanks & regards,
Narayana Karthik

 

[HP1701]

Hi there Maxwell, thanks for trying to help me out. I just want to understand this too rather than just use other people's work with no effort on my part, but I can't quite see what this one does. How does this bit identify the rows, in laymans terms what does it do?

For i = Selection.Rows.Count To 1 Step -1

If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

Many thanks
HP

 

[HP1701]

Hi Narayana
Thanks for this. I can see what it does too which is great for my learning. It doesn't quite work yet, but it's definitely given me a foundation to play with now. A bit of trial and error learning is always good.

May I ask another question please?
On the Find(what:="0", piece, can I use this for values less 0 too, so maybe a Find(what:="<0", is that the right syntax?
Cheers
HP

 

[maxwell_r_ryan]

Hi there,


What this part of the code does is it initiates a loop that will loop until it reaches the end of your selection. each time it goes through and checks if the cell is blank if it is it deletes if not it just continues looping until it reaches the end of the column. For this to work you must select the entire column you want to run this macro on.


Cheers,
Max

 

[HP1701]

Aaah, brilliant, thanks very much. So that would work without the need to sort, very handy.
Cheers Max
HP