Filter and Sorting

[Adrian Low]

[FONT=&quot]Hi I have two questions.[/FONT]

• Create filter to find any blanks cells and delete the entire row with it after which show all data
  
• Sort in one of the input on Range("E3:E") from A to Z

[FONT=&quot]I tried this but keeps getting error "Method of object worksheet error"[/FONT]
<code style="box-sizing: inherit; font-family: Consolas, "Courier New", Courier, monospace; font-size: 1em; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;"> Set wsInput = Workbooks("InputB.xls").Worksheets("HC_MODULAR_BOARD_20180112")
Set wsOutput = Workbooks("Output.xls").Worksheets("Sheet1")
Set Ws2 = Workbooks("InputA.xls").Worksheets("Sheet0")


With wsInput

'Error starts from here
wsInput.Range("F3:F").AutoFilter Criteria1:="="
wsInput.Range("F3:F").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
wsInput.ShowAllData

LastRow = wsInput.Cells(wsInput.Rows.Count, "E").End(xlUp).Row
Rows("3:LastRow").Select
.Sort.SortFields.Clear
.Sort.SortFields.Add Key _
:=Range("E3:LastRow"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal

With wsInput.Sort
.SetRange Range("A2:LastRow")
.header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With

</code>[FONT=&quot]I believe my sorting have some issues too please let me know if any of you see any mistakes.

[/FONT]
[FONT=&quot]Many thanks,[/FONT]
[FONT=&quot]Adrian[/FONT]

 

[Fluff]

@Adrian Low
In future when posting code, please use code tags, the #icon in the reply window.
Thanks

 

[JustynaMK]

Hi Adrian,

Apologies for the delay, I was away for a few days. I checked your code with my colleagues and we still cannot replicate your issue - on a sample data in cells E3:G10, with a few blank cells (G6, G8, G9) we were able to use your macro and successfully remove rows 6, 8 and 9.
The only error we got is on wsInput.ShowAllData, as second line (.Delete) already shows all data (i.e. removes filter from column G) so VBA cannot proceed with ShowAllData command.

Sub foo()
    Dim LastRow As Long


    LastRow = wsInput.Cells(wsInput.Rows.Count, "E").End(xlUp).Row


    With wsInput
        wsInput.Range("G3:G" & LastRow).AutoFilter Field:=1, Criteria1:=""
        wsInput.Range("G3:G" & LastRow).SpecialCells(xlCellTypeVisible).Delete
        'wsInput.ShowAllData
    End With
End Sub

I think the only solution for now is to share your file (remove any sensitive data) so that I can replicate the issue on my side.

Take care.