Creating a sorting and printing macro using VBA

[bmt216a]

Hello everyone,

I want to create a sorting and printing macro that is able to dynamically adjust the range of of values that is sorted and printed. I have a really simple worksheet with 4 columns A-D and varying rows at which the data ends. I have attached a screenshot. The data needs to be sorted by Column C, Cell values, Smallest to largest. The important thing here is to sort Column B along with it. Below is my code. I need help to adjust the range so that it picks up whatever the last row is and then sorts and prints the entire worksheet. Can anybody help? Thank you

?

Sub Macro22()
'
' Macro22 Macro
'

'
    Range("B5:C5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("10-6-18_Coax_Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("10-6-18_Coax_Data").Sort.SortFields.Add2 Key:= _
        Range("C5:C94" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("10-6-18_Coax_Data").Sort
        .SetRange Range("B4:C94" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("F4").Select
End Sub
Sub Macro23()
'
' Macro23 Macro
'

'
    Range("A1").Select
    ActiveWindow.SmallScroll Down:=51
    Range("A1:D62").Select
    ActiveWindow.SmallScroll Down:=27
    Range("A1:D94").Select
    ActiveWindow.SmallScroll Down:=-114
    Selection.PrintOut Copies:=1, Collate:=True
End Sub

 

[rlv01]

Perhaps something like this.

Sub SortCol()
    Dim WS As Worksheet
    Dim rng As Range
    
    Set WS = ThisWorkbook.Worksheets("10-6-18_Coax_Data")
    
    With WS
        Set rng = .Range("A4:D" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With

    rng.Sort Key1:=rng.Columns(3), Order1:=xlAscending, Header:=xlYes
End Sub

Sub PrintSheet()
    Dim WS As Worksheet

    Set WS = ThisWorkbook.Worksheets("10-6-18_Coax_Data")
    WS.PrintOut
End Sub

 

[bmt216a]

Perhaps something like this.

Sub SortCol()
    Dim WS As Worksheet
    Dim rng As Range
   
    Set WS = ThisWorkbook.Worksheets("10-6-18_Coax_Data")
   
    With WS
        Set rng = .Range("A4:D" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With

    rng.Sort Key1:=rng.Columns(3), Order1:=xlAscending, Header:=xlYes
End Sub

Sub PrintSheet()
    Dim WS As Worksheet

    Set WS = ThisWorkbook.Worksheets("10-6-18_Coax_Data")
    WS.PrintOut
End Sub

Hello, thank you for your response. After messing around with it a bit I have found the solution for the correct sorting. However, the print function has become a separate macro. I would like there to be one macro for both sort and print and one button I can press to automate both processes for me. Is there a way to do that? I will attach the edited code and the macro window.

Sub SortCol()

    Dim WS As Worksheet

    Dim rng As Range

  

    Set WS = ThisWorkbook.Worksheets("Data")

  

    With WS

        Set rng = .Range("B4:C4" & .Range("C" & .Rows.Count).End(xlUp).Row)

    End With



    rng.Sort Key1:=rng.Columns(2), Order1:=xlAscending, Header:=xlYes

End Sub

Sub PrintSheet()

    Dim WS As Worksheet



    Set WS = ThisWorkbook.Worksheets("Data")

    WS.PrintOut

End Sub

 

[bmt216a]

Hello, I actually figured it out. I just used the call function. Thank you for your help.