Selecting Rows based on cell criteria

  • Hello all, I'm sure there is simple code for this but I have excel spreadsheets like the sample attached that are consistant in the types of fields but the only data I want to strip out are the rows where column A has a "G" in the cell, I tried using this
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select


    but this doesn't work when there is only one row of data. The number of rows with valid data will vary each day. I then tried an if, then statement and a loop but I could never get the loop to stop. I basically want a macro which parses through the worksheet and selects the rows that have that "G" in column A so I can copy them and paste them in another worksheet. Any help is appreciated.

  • Re: Selecting Rows based on cell criteria


    hi Amber,


    what do you mean by "strip out"?


    anyway here's a code to get the range you want and generate
    the address if containing "G"


    hope this helps

    Cheers
    ___________
    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • Re: Selecting Rows based on cell criteria


    Hi, I don't want to strip out anything from this particular worksheet I just want to select those rows that meet the criteria of having "G" in column A and so that my next set of instructions will be copying them and pasting into another worksheet, which I can handle that part, it's just getting to the point of selecting those rows. I tried adding this to your code and get an endless loop. I just can't get this looping stuff.


    Sub Macro3()
    Dim rngA As Range
    Dim cell As Range

    Set rngA = Range("A1", Range("A65536").End(xlUp))
    For Each cell In rngA
    If cell.Value = "G" Then

    Rows.Select

    End If
    Next cell

    End Sub

  • Re: Selecting Rows based on cell criteria


    hi Amber,


    try the code:


    the above copies the entire row if a cell in col A="G",
    activates a Amber.xls and pastes the row to the empty row in sheet1


    btw, its a good practice to use the code tags when writing a code in the post
    makes it easier to read


    hth

    Cheers
    ___________
    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • Re: Selecting Rows based on cell criteria


    Hi,


    With a relatively small data range you probably won't notice a significant delay with a cell by cell loop, especially if you switch off Screenupdating. However, if you are dealing with larger data ranges you will find it much quicker to make use of either Find or the good old AutoFilter.


    Like this :[vba]Sub Using_Find()
    Dim rngData As Range
    Dim rngFound As Range, firstAddress As String
    Dim wsNew As Worksheet
    Const strFindMe As String = "G"

    Application.ScreenUpdating = False

    With ThisWorkbook.Worksheets("F5D860")
    Set rngData = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    Set wsNew = ThisWorkbook.Worksheets.Add

    With rngData
    Set rngFound = .Find(strFindMe, LookIn:=xlValues)
    If Not rngFound Is Nothing Then
    firstAddress = rngFound.Address
    Do
    rngFound.EntireRow.Copy
    wsNew.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    Set rngFound = .FindNext(rngFound)
    Loop While Not rngFound Is Nothing And rngFound.Address <> firstAddress
    End If
    End With

    Application.ScreenUpdating = True

    End Sub[/vba]Or like this (note that I added a header row to the worksheet):[vba]Sub Using_AutoFilter()
    Dim rngData As Range
    Dim rngFound As Range
    Dim wsNew As Worksheet
    Const strFindMe As String = "G"

    Application.ScreenUpdating = False

    With ThisWorkbook.Worksheets("F5D860")
    Set rngData = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    Set wsNew = ThisWorkbook.Worksheets.Add
    rngData.AutoFilter Field:=1, Criteria1:=strFindMe
    Set rngFound = .AutoFilter.Range.Offset(1, 0) _
    .Resize(.AutoFilter.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    rngFound.EntireRow.Copy
    wsNew.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    rngFound.AutoFilter
    End With

    Application.ScreenUpdating = True

    End Sub[/vba]HTH

  • Re: Selecting Rows based on cell criteria


    Thank you guys for trying to help me. Your code works to some degree but I'm still having some difficulty. Please see my jpeg of a print screen of all I want to do. Without worrying about copying and pasting the selected data anywhere, all I want the macro right now to do is "select" only those rows that meet the criteria of having a "G" in their column A. Once I can see how to get to this point, I can take it from here. Thanks again for all your help. Excuse the poor qualilty of the jpeg, i had to shrink it down so much, it's hard to see but I think my idea will come across.

  • Re: Selecting Rows based on cell criteria


    Try adapting...

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Re: Selecting Rows based on cell criteria


    Quote from dangelor

    Try adapting...


    This code does part of the work for me - what would I need to do, if I need to select ONLY a portion of the row (say Cx:Ex, where x is that particular row) instead of the entire row? I am a new member and Excel VBA always stumped me. Any help will be greatly appreciated. :confused:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!