VBA loop to select rows based on cell value?

user_15477601 0
Viewed 285

I have data from a machine which I import into excel and it gives a dataset which is around 7 columns wide and 66000 rows deep. (sometimes it will be more, sometimes less).

What I want to is to be able to select data based on Column E and then that will select all the rows so far, I can mirror that across to another sheet.

Column E by the way basically contains a couple of thousand rows of zeros, then it changes to a number greater than zero for a few thousand rows. Then it loops back to zero etc. It will do this cycle around 25 times per data set. (Always different between number of rows per loop etc).

So what I think what I want is basically two loops (I have never done any loop work before I only usually use VBA for graphing and other things etc).

Loop 1:

Look at column E if it equals zero then select the row.

Do this until Column E Does not equal zero

Mirror this selection (I can use the Range.Resize method)into different area

Delete the selection

Shift cells up

I would then call a similar loop for the values greater than zero and then recall loop 1 again and do this 25 times (but that doesn't matter here)...

What I want help with is:

How do I create a loop to select cells/rows of data based on the value of a cell/column? The loop should end once it has selected the cells because each group will be moved to a different area on the work book...

Or is there something better to use than loops?

Hope this all makes sense and I dont sound to stupid...

Cheers

vba excel loops
1 Answers

Retrieve Non-Zero Areas

Option Explicit

Sub RetrieveNonZeroAreas()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    If sws.FilterMode Then sws.ShowAllData
    
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    Dim sdrg As Range: Set sdrg = srg.Resize(srg.Rows.Count - 1).Offset(1)
    
    srg.AutoFilter 5, "<>0"
    
    Dim svdrg As Range
    On Error Resume Next
        Set svdrg = sdrg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    sws.AutoFilterMode = False
    
    If svdrg Is Nothing Then Exit Sub
    
    Dim arg As Range
    For Each arg In svdrg.Areas
        ' Now do what you need to do with each of the 25 or so datasets, e.g.:
        
        Debug.Print arg.Address
        
    Next arg
    
    MsgBox "Data... done.", vbInformation
    
End Sub
user_98140690
Related