Exit Sub if Column Value Nothing

P

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi can anyone help?

I have this UserForm code that Selects Rows based on Column Values and I want to add an exit sub if nothing is found for 'findcolB' as it goes to Debug when nothing id found.

Code: 
Sub SelectRowsAB()

    Dim findcolA As Long
    Dim findcolB As Long
    Dim Copyrange
    Dim rownum
    Dim Startrow
    Dim lastrow

    Application.ScreenUpdating = False
    
    Sheets("Data").Select

    findcolA = Me.lbColA.Value
    findcolB = Me.lbColB.Value

    rownum = 2
        
    Do Until Sheets("Data").Cells(rownum, 1).Value = ""
        If Sheets("Data").Cells(rownum, 1) = findcolA And Sheets("Data").Cells(rownum, 2) = findcolB Then
            Startrow = rownum
            GoTo FindLastRow
        End If
        rownum = rownum + 1
    Loop

FindLastRow:
    Do Until Sheets("Data").Cells(rownum, 1) = ""
        If Sheets("Data").Cells(rownum, 1) = findcolA And Sheets("Data").Cells(rownum, 2) = findcolB Then
            lastrow = rownum
        End If
        rownum = rownum + 1
    Loop

        Let Copyrange = "A" & Startrow & ":" & "C" & lastrow
        Sheets("Data").Range(Copyrange).Copy
        Sheets("Data2").Range("B3").PasteSpecial xlPasteValues
        Application.CutCopyMode = False

        Range("A1").Select

    Application.ScreenUpdating = True

End Sub

Any help would be appreciated

Regards

pwill
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Try:
Code: 
Sub SelectRowsAB()
    
    Dim x       As Long
    Dim LR      As Long
    Dim rng     As Range
                     
    'findcolA = Me.lbColA.Value
    'findcolB = Me.lbColB.Value
    
    If Len(Me.lbColA.Value) * Len(lbColB.Value) = 0 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    With Sheets("Data")
        LR = .Cells(.Rows.count, 1).End(xlUp).row
        For x = 2 To LR
            If .Cells(x, 1) = Me.lbColA.Value And .Cells(x, 2) = Me.lbColB.Value Then
                If rng Is Nothing Then
                    Set rng = .Cells(x, 1)
                Else
                    Set rng = rng.Resize(x - rng.row + 1, 3)
                    Exit For
                End If
            End If
        Next x
        .Select
        .Cells(1, 1).Select
    End With
    
    Sheets("Data2").Cells(3, 2).Resize(rng.Rows.count, rng.Columns.count).Value = rng.Value
    Set rng = Nothing

    Application.ScreenUpdating = True

End Sub
 
Last edited:
Upvote 0
Thanks again JackDanIce,

Not sure where I'm going wrong? Didn't work at this end

thanks for trying, much appreciated :)

regards
pwill
 
Upvote 0
Does it error? Can you give an example of what the start and end address in column A might be

Slight adjustment, try:
Code: 
Sub SelectRowsAB()
    
    Dim x       As Long
    Dim LR      As Long
    Dim rng     As Range
                    
    Dim a       As Variant
    Dim b       As Variant
    
    a = Me.lbColA.Value
    b = Me.lbColB.Value
    
    If Len(a) * Len(b) = 0 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    With Sheets("Data")
        LR = .Cells(.Rows.count, 1).End(xlUp).row
        For x = 2 To LR
            If .Cells(x, 1).Value = a And .Cells(x, 2).Value = b Then
                If rng Is Nothing Then
                    Set rng = .Cells(x, 1)
                Else
                    Set rng = Range(rng, .Cells(x, 1)).Resize(, 3)
                    Exit For
                End If
            End If
        Next x
        .Select
        .Cells(1, 1).Select
    End With
    
    Sheets("Data2").Cells(3, 2).Resize(rng.Rows.count, rng.Columns.count).Value = rng.Value
    Set rng = Nothing

    Application.ScreenUpdating = True

End Sub
 
Last edited:
Upvote 0
Hi JackDanIce,

Thanks for your update

Here is a better example of my data I have changed 'lbColA' to choose from Column E and 'lbColB' to choose from Column F

IE
On the user Form if I choose 0 in 'lbColA' and 1 in 'lbColB' then rows C5:K7 are copied from 'Data Sheet' to 'Data2 Sheet' B3:J


<tbody>
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, width: 64, align: center"] A [/TD]
[TD="class: xl67, width: 64, align: center"] B [/TD]
[TD="class: xl67, width: 64, align: center"] C [/TD]
[TD="class: xl67, width: 64, align: center"] D [/TD]
[TD="class: xl67, width: 64, align: center"] E [/TD]
[TD="class: xl67, width: 64, align: center"] F [/TD]
[TD="class: xl67, width: 64, align: center"] G [/TD]
[TD="class: xl67, width: 64, align: center"] H [/TD]
[TD="class: xl67, width: 64, align: center"] I [/TD]
[TD="class: xl67, width: 64, align: center"] J [/TD]
[TD="class: xl67, width: 64, align: center"] K [/TD]
[TD="class: xl67, width: 64, align: center"] L [/TD]
[TD="class: xl67, width: 64, align: center"] M [/TD]
[TD="class: xl67, width: 64"]N[/TD]

[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] Name [/TD]
[TD="class: xl64, align: center"] Date [/TD]
[TD="class: xl63, align: center"] Ref [/TD]
[TD="class: xl63, align: center"] Ref [/TD]
[TD="class: xl65, align: center"] A [/TD]
[TD="class: xl65, align: center"] B [/TD]
[TD="class: xl65, align: center"] C [/TD]
[TD="class: xl65, align: center"] D [/TD]
[TD="class: xl65, align: center"] E [/TD]
[TD="class: xl65, align: center"] F [/TD]
[TD="class: xl65, align: center"] G [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl66, align: center"] 10/10/17 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl66, align: center"] 10/10/17 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl66, align: center"] 11/10/17 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl66, align: center"] 14/10/17 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl66, align: center"] 15/10/17 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl66, align: center"] 15/10/17 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl66, align: center"] 18/10/17 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 9 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl66, align: center"] 18/10/17 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 10 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl66, align: center"] 19/10/17 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 11 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67, align: center"] 12 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67, align: center"]
[/TD]
[TD="class: xl67"][/TD]

</tbody>

and in this example if I choose 0 in 'lbColA' and 1 in 'lbColB' I want to exit sub as 1 is not found in column F alongside 0 in column E


<tbody>
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, width: 64, align: center"] A [/TD]
[TD="class: xl69, width: 64, align: center"] B [/TD]
[TD="class: xl69, width: 64, align: center"] C [/TD]
[TD="class: xl69, width: 64, align: center"] D [/TD]
[TD="class: xl69, width: 64, align: center"] E [/TD]
[TD="class: xl69, width: 64, align: center"] F [/TD]
[TD="class: xl69, width: 64, align: center"] G [/TD]
[TD="class: xl69, width: 64, align: center"] H [/TD]
[TD="class: xl69, width: 64, align: center"] I [/TD]
[TD="class: xl69, width: 64, align: center"] J [/TD]
[TD="class: xl69, width: 64, align: center"] K [/TD]
[TD="class: xl69, width: 64, align: center"] L [/TD]
[TD="class: xl69, width: 64, align: center"] M [/TD]
[TD="class: xl69, width: 64, align: center"] N [/TD]

[TD="class: xl69, align: center"] 1 [/TD]
[TD="class: xl65, align: center"] Name [/TD]
[TD="class: xl66, align: center"] Date [/TD]
[TD="class: xl65, align: center"] Ref [/TD]
[TD="class: xl65, align: center"] Ref [/TD]
[TD="class: xl67, align: center"] A [/TD]
[TD="class: xl67, align: center"] B [/TD]
[TD="class: xl67, align: center"] C [/TD]
[TD="class: xl67, align: center"] D [/TD]
[TD="class: xl67, align: center"] E [/TD]
[TD="class: xl67, align: center"] F [/TD]
[TD="class: xl67, align: center"] G [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 2 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl68, align: center"] 10/10/17 [/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 3 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl68, align: center"] 10/10/17 [/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 4 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl68, align: center"] 11/10/17 [/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 5 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl68, align: center"] 14/10/17 [/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 6 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl68, align: center"] 15/10/17 [/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 7 [/TD]
[TD="class: xl65, align: center"] 0 [/TD]
[TD="class: xl68, align: center"] 15/10/17 [/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl67, align: center"] 0 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl67, align: center"] 2 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 8 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl68, align: center"] 18/10/17 [/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 9 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl68, align: center"] 18/10/17 [/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 10 [/TD]
[TD="class: xl65, align: center"] 1 [/TD]
[TD="class: xl68, align: center"] 19/10/17 [/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl65, align: center"]
[/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl67, align: center"] 1 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 11 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

[TD="class: xl69, align: center"] 12 [/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]
[TD="class: xl69, align: center"]
[/TD]

</tbody>

Hope that makes sense

regards

pwill
 
Last edited:
Upvote 0
Your original code wasn't comparing columns E and F, it was comparing A and B. Try below, changes highlighted:
Rich (BB code): 
Sub SelectRowsAB()
    
    Dim x       As Long
    Dim LR      As Long
    Dim rng     As Range
                    
    Dim a       As Variant
    Dim b       As Variant
    
    a = Me.lbColA.Value
    b = Me.lbColB.Value
    
    If Len(a) * Len(b) = 0 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    With Sheets("Data")
        LR = .Cells(.Rows.count, 1).End(xlUp).row
        For x = 2 To LR
            If .Cells(x, 5).Value = a And .Cells(x, 6).Value = b Then
                If rng Is Nothing Then
                    Set rng = .Cells(x, 1)
                Else
                    Set rng = Range(rng, .Cells(x, 1)).Resize(, 3)
                    Exit For
                End If
            End If
        Next x
        .Select
        .Cells(1, 1).Select
    End With
    
    If Not rng Is Nothing Then Sheets("Data2").Cells(3, 2).Resize(rng.Rows.count, rng.Columns.count).Value = rng.Value
    Set rng = Nothing

    Application.ScreenUpdating = True

End Sub
 
Last edited:
Upvote 0
Thanks again JackDanIce,

I think I will give up on this one, it just doesn't want to play but thank you for your time much appreciated :)

kind regards
pwill
 
Upvote 0
Hi JackDanIce,

Thank you for the fix, works a treat :)

Regards
pwill

Code: 
Hi,

I'm guessing it errors on "Let Copyrange" etc. at the moment?

Try this.

     Code:

    Sub SelectRowsAB()

    Dim findcolA As Long
    Dim findcolB As Long
    Dim Copyrange
    Dim rownum
    Dim Startrow
    Dim lastrow


    Application.ScreenUpdating = False
    
    Sheets("Data").Select


    findcolA = Me.lbColA.Value
    findcolB = Me.lbColB.Value


    rownum = 2
        
    Do Until Sheets("Data").Cells(rownum, 1).Value = ""
        If Sheets("Data").Cells(rownum, 1) = findcolA And Sheets("Data").Cells(rownum, 2) = findcolB Then
            Startrow = rownum
            GoTo FindLastRow
        End If
        rownum = rownum + 1
    Loop


FindLastRow:
    Do Until Sheets("Data").Cells(rownum, 1) = ""
        If Sheets("Data").Cells(rownum, 1) = findcolA And Sheets("Data").Cells(rownum, 2) = findcolB Then
            lastrow = rownum
        End If
        rownum = rownum + 1
    Loop


On Error GoTo Getout
        Let Copyrange = "A" & Startrow & ":" & "C" & lastrow
        Sheets("Data").Range(Copyrange).Copy
        Sheets("Data2").Range("B3").PasteSpecial xlPasteValues
        Application.CutCopyMode = False


        Range("A1").Select
        
Getout:


    Application.ScreenUpdating = True


End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Combining VBA Codes
Replies
4
Views
295
DanteAmor
DanteAmor
J
  • Solved
VBA 3 Column Table and Display each column separately in 3 list boxes.
Replies
2
Views
270
JulianvO
J
C
  • Question Question
VBA Sum and replace rather than Copy Destination
Replies
4
Views
412
Alex Blakenburg
A
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
887
Gajendran Yadhav
G
N
  • Question Question
Myrna Larson-Permutations and Combinations
Replies
0
Views
305
Norman A
N

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top