VBA only sort if there is data

[Isabella]

Hi, i need a condition in the below code to only proceed to sort if there is data from row 6 onwards else exit sub

Sub Test()

Dim lDstRowNum As Long
    With Sheets(1)
        lDstRowNum = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lDstRowNum > 5 Then
            With .Range("P6:P" & lDstRowNum)
                .Formula = "=MATCH(A6,rngSecSort,0)"
            End With
            .Range(gGroupHeader).CurrentRegion.Sort Key1:=.Range("P6"), Order1:=xlAscending, _
            Key2:=.Range("E6"), Order2:=xlAscending, Key3:=.Range("G6"), Order3:=xlDescending, Header:=xlGuess, _
            ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        End If
        With .Range("P6:P" & lDstRowNum)
            .ClearContents
        End With
    End With
End Sub

 

[Desu Nota from Columbus]

Sub Test()

Dim lDstRowNum As Long
    With Sheets(1)
        lDstRowNum = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lDstRowNum > 5 And IsEmpty(Rows(6)) = False Then
            With .Range("P6:P" & lDstRowNum)
                .Formula = "=MATCH(A6,rngSecSort,0)"
            End With
            .Range(gGroupHeader).CurrentRegion.Sort Key1:=.Range("P6"), Order1:=xlAscending, _
            Key2:=.Range("E6"), Order2:=xlAscending, Key3:=.Range("G6"), Order3:=xlDescending, Header:=xlGuess, _
            ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        Else: Exit Sub
        End If
        With .Range("P6:P" & lDstRowNum)
            .ClearContents
        End With
    End With
End Sub

See if this works for you. I made the assumption that if there was no data in row 6, then there isn't data to be sorted in rows 7-End.

A few questions/comments:

1) What do you mean when you say 'no data'? Will the cells be totally blank (absolutely nothing in them, including formulas that return "")? ...IsBlank(Rows(6)) looks to see if the entire row is devoid of any characters.

2) I used the entire Row(6) as the criteria. If you have a more definite range to substitute in, change rows(r).

 

[Isabella]

Thanks this works as required

Sub Test()
 
Dim lDstRowNum As Long
    With Sheets(1)
        lDstRowNum = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lDstRowNum > 5 And IsEmpty(Rows(6)) = False Then
            With .Range("P6:P" & lDstRowNum)
                .Formula = "=MATCH(A6,rngSecSort,0)"
            End With
            .Range(gGroupHeader).CurrentRegion.Sort Key1:=.Range("P6"), Order1:=xlAscending, _
            Key2:=.Range("E6"), Order2:=xlAscending, Key3:=.Range("G6"), Order3:=xlDescending, Header:=xlGuess, _
            ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        Else: Exit Sub
        End If
        With .Range("P6:P" & lDstRowNum)
            .ClearContents
        End With
    End With
End Sub

See if this works for you. I made the assumption that if there was no data in row 6, then there isn't data to be sorted in rows 7-End.

A few questions/comments:

1) What do you mean when you say 'no data'? Will the cells be totally blank (absolutely nothing in them, including formulas that return "")? ...IsBlank(Rows(6)) looks to see if the entire row is devoid of any characters.

2) I used the entire Row(6) as the criteria. If you have a more definite range to substitute in, change rows(r).