Need Macro to Carry To The Last Line in Sheet

[DCBUS]

Below is the Macro I am using, but currently it only carries down to row 12. Can someone please help me modify so the Macro carries to last last line of data in the sheet. I will be using on multiple sheets in the future, so the row count may vary.

Sub Byers()'
' Byers Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    Range("C2:C12").Select
    Selection.NumberFormat = "0.00"
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0"
    Columns("D:D").Select
    Selection.Cut
    Range("G1").Select
    Selection.Insert Shift:=xlToRight
    Range("E2:E12").Select
    Application.Run "PERSONAL.XLSB!RemoveTags"
    Selection.Replace What:=" ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="[/n]", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=".", Replacement:=". ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("F2:F12").Select
    Application.Run "PERSONAL.XLSB!SeparateDataBySemiColon"
    Range("H17").Select
End Sub

Thank you,
Jr

 

[Fluff]

Hi
Untested but try

Sub Byers() '
' Byers Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    Dim UsdRws As Long
    
    UsdRws = Range("C" & Rows.Count).End(xlUp).Row
    Range("C2:C" & UsdRws).Select
    Selection.NumberFormat = "0.00"
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0"
    Columns("D:D").Select
    Selection.Cut
    Range("G1").Select
    Selection.Insert Shift:=xlToRight
    Range("E2:E" & UsdRws).Select
    Application.Run "PERSONAL.XLSB!RemoveTags"
    Selection.Replace What:=" ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="[/n]", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=".", Replacement:=". ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("F2:F" & UsdRws).Select
    Application.Run "PERSONAL.XLSB!SeparateDataBySemiColon"
    Range("H17").Select
End Sub

 

[DCBUS]

Hello,

Thank you for the suggestion. I tried the Macro, but I get error 400.

Thanks JR

 

[Fluff]

What line do you get the error on?

 

[DCBUS]

Good Morning,

Thank you for the response. I rebooted the system and no longer get the error?? All seems to work, except on column "F", which on this sheet, there are 23 rows, but it only executes down to row 13.

Thank you very much,
JR

 

[Fluff]

Do all columns have the same amount of data (ie 23 rows)?

 

[DCBUS]

Thank you for all the help. Seems to work fine.

 

[Fluff]

Glad to help & thanks for the feedback