Excel VBA Applying Sort not working

[Nu2Java]

Hi All - I am using the below code to combine a few steps on a sheet. The last part of the macro, I am trying apply a sort on column B but I am getting strange results. Some of my data is at the top of the sheet and the rest is several thousand lines down with all blank data in between. Not sure where I am going wrong. If I do a manual sort, everything is just fine.

Public Const vbLightGreen As Long = 13434828

Sub ConditionalFormatDuplicates()

Sheets("Combined").Activate

Dim rg As Range
Dim uv As UniqueValues


'specify range to apply conditional formatting
Set rg = Range("A1:B50000")

'clear any existing conditional formatting
rg.FormatConditions.Delete

'identify duplicate values in range A2:A11
Set uv = rg.FormatConditions.AddUniqueValues
uv.DupeUnique = xlDuplicate

'apply conditional formatting to duplicate values
uv.Interior.Color = vbLightGreen
uv.Font.Color = vbBlack
uv.Font.Bold = True


'Aut-Fit Cells
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit


Call DeleteRowWithContents


End Sub

Sub DeleteRowWithContents()

'Remove Rows containing...
With ActiveSheet
    .AutoFilterMode = False
    With Range("e1", Range("e" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*Dedicated*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With


'Sort Data
Dim LastRow As Long
    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("A1" & LastRow).Sort Key1:=Range("B1"), _
       Order1:=xlDescending, Header:=xlNo


End Sub

 

[vletm]

Nu2Java​

Verify ...
that Your range is one cell in A-column Range("A1" & LastRow)
eg if LastRows value is 1234 then Range("A1"&1234) same as Range("A11234")
You'll try to sort it with B1.
What range would You try to sort?

 

[Marc L]

Not sure where I am going wrong.

Hi, just a logic issue … You can use CurrentRegion or UsedRange as explained in VBA help.​

​

 

[vletm]

Nu2Java​

Did You sort manually same way as with Your code ( one cell )?
You can always record Your manually sorting and compare it with Your code.

 

[p45cal]

I suspect that Sort line should be:
Range("A1:H" & LastRow).Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlYes

 

[Nu2Java]

Thanks all for your help. It did end up being the 'Range("A1:H" & LastRow)' that I overlooked.