Selecting All Cells Even If Non Contiguous

[53fordtruck]

I get a daily company .csv report that has 10 columns but varying numbers of rows (anywhere from 1200-1500). There are many rows and cells that are blank. I would like to select from cell A3 to the end, including the blank cells, and then alphabetize the data based on column A. I can not figure out how to get all the data selected in VBA. I've tried getting xltoRight and xlDown to work, but keep getting stopped at the first blank row or column. Can someone please point me in the right direction? Thanks.

Sample in image.

Jerry

 

[kevin9999]

Try the following on a copy of your workbook.

Option Explicit
Sub fordtruck53()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change sheet name to suit ***
    
    With ws.Range(ws.Cells(3, 1), ws.Cells(ws.Cells(Rows.Count, 1).End(xlUp).Row, _
    ws.Cells(1, Columns.Count).End(xlToLeft).Column))
        .Sort Key1:=ws.Range("A3"), order1:=xlAscending, Header:=xlNo
    End With
End Sub

 

[Trebor76]

Hi Jerry,

Welcome to MrExcel!!

Here's my attempt:

Option Explicit
Sub Macro1()

    Dim wsSrc As Worksheet
    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    Set wsSrc = ThisWorkbook.Sheets(1) 'Ok to reference the first sheet as a CSV file can only have one sheet
    lngLastRow = wsSrc.Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'Refer https://stackoverflow.com/questions/21451458/vba-excel-sort-range-by-specific-column
    wsSrc.Range("A3:J" & lngLastRow).Sort key1:=wsSrc.Range("A3:J" & lngLastRow), Order1:=xlAscending, Header:=xlNo
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[footoo]

If you prefer to do manually, select columns A:H, sort (with header), insert row 2.
Or use this to create a macro with the macro recorder.

 

[Peter_SSs]

What about ..

Sub Sort_Data()
  ActiveSheet.UsedRange.Sort Key1:=Columns(1), Order1:=xlAscending, Header:=xlYes
End Sub

Or if you really do want to only start at row 3 then

Sub Sort_Data_v2()
  ActiveSheet.UsedRange.Offset(2).Sort Key1:=Cells(3, 1), Order1:=xlAscending, Header:=xlNo
End Sub

 

[53fordtruck]

What about ..

Sub Sort_Data()
  ActiveSheet.UsedRange.Sort Key1:=Columns(1), Order1:=xlAscending, Header:=xlYes
End Sub

Or if you really do want to only start at row 3 then

Sub Sort_Data_v2()
  ActiveSheet.UsedRange.Offset(2).Sort Key1:=Cells(3, 1), Order1:=xlAscending, Header:=xlNo
End Sub

Awesome!! I never heard of UsedRange. That's a big one for me to know. BIG THANKS!

Jerry

 

[53fordtruck]

If you prefer to do manually, select columns A:H, sort (with header), insert row 2.
Or use this to create a macro with the macro recorder.

I am looking for this to be automatic. Thanks.

 

[Peter_SSs]

Awesome!! I never heard of UsedRange. That's a big one for me to know. BIG THANKS!

Jerry

You're welcome. Thanks for the follow-up.

 

[53fordtruck]

Hi Jerry,

Welcome to MrExcel!!

Here's my attempt:

Option Explicit
Sub Macro1()

    Dim wsSrc As Worksheet
    Dim lngLastRow As Long
   
    Application.ScreenUpdating = False
   
    Set wsSrc = ThisWorkbook.Sheets(1) 'Ok to reference the first sheet as a CSV file can only have one sheet
    lngLastRow = wsSrc.Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    'Refer https://stackoverflow.com/questions/21451458/vba-excel-sort-range-by-specific-column
    wsSrc.Range("A3:J" & lngLastRow).Sort key1:=wsSrc.Range("A3:J" & lngLastRow), Order1:=xlAscending, Header:=xlNo
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Thanks, Robert! This mostly worked as well. I had to modify the Set wsSrc line since I created "Sheet2" as part of the way I reorganized the columns in to the correct order (when I save the sheet, I'll convert is to .xls or .xlm):

Dim wsSrc As Worksheet
Dim lngLastRow As Long

Application.ScreenUpdating = False

Set wsSrc = Worksheets("Sheet2") 'I previously created "Sheet2" as a way to copy and paste columns in the correct order
lngLastRow = wsSrc.Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Refer VBA Excel sort range by specific column
wsSrc.Range("A3:J" & lngLastRow).Sort key1:=wsSrc.Range("A3:J" & lngLastRow), Order1:=xlAscending, Header:=xlNo

Application.ScreenUpdating = True

This site is awesome, plenty of suggestions to the same issue. I have some serious work to do learning about the Dim and process of working with them.

Much appreciated.

Jerry

 

[53fordtruck]

Try the following on a copy of your workbook.

Option Explicit
Sub fordtruck53()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change sheet name to suit ***
   
    With ws.Range(ws.Cells(3, 1), ws.Cells(ws.Cells(Rows.Count, 1).End(xlUp).Row, _
    ws.Cells(1, Columns.Count).End(xlToLeft).Column))
        .Sort Key1:=ws.Range("A3"), order1:=xlAscending, Header:=xlNo
    End With
End Sub

Kevin9999, thanks!

This option worked as well. I changed the Set ws as "Sheet2" and it worked straight away.

Much appreciated.

Jerry