Rewrite of macro wizard 2007

[iknowyourthere]

Never noticed the problem before but with 2007 it has stuck out. when I run this macro that i use from just recording deleting and moving things i notice that my last cell is like 1000000+ but data ends at 2-5K. I have tried to use the code to clean up activesheet.usedrange but Ctlr-end still brings me to G1million because the record wizard selected col. Can this be written to be dynamic as the amount of data changes every month but never exceeds the G col

    Range("A1:H6").Select
    Selection.Delete Shift:=xlUp
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Copy
    Columns("E:E").Select
    ActiveSheet.Paste
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Columns("A:A").Select
    Selection.Replace What:=";*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("B:B").Select
    Selection.NumberFormat = "0"
    Columns("A:A").ColumnWidth = 54
    Columns("A:A").ColumnWidth = 67.86
    Selection.ColumnWidth = 15.14
    Columns("C:C").Select
    Selection.ColumnWidth = 26.71
    Columns("E:E").Select
    Selection.Replace What:="c", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.NumberFormat = "0.00"
    Range("G2").Select
    Selection.NumberFormat = "0.00"
    ActiveCell.FormulaR1C1 = "-1"
    Range("G2").Select
    Selection.Copy
    Columns("E:E").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.DisplayZeros = False
    Columns("A:E").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal

 

[Joe4]

To find the last row in column G with something in it, you can use the following code:

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

By the way, you can speed up your code by getting rid of most of your SELECT statements, i.e.
this:

    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft

can be replaced with this:

    Columns("A:A").Delete Shift:=xlToLeft

 

[iknowyourthere]

Thank you cleaned up the coding, I tried adding the

LastRow=Cells(Rows.Count,"G").End(xlUp).Row</pre>

at the end of the macro and nothing changed. i also added a range A1.select as the second last line as the last thing it did was highlight columns A-E and then added the above code, all to no avail also tried to save the file and then open to see if it would register a last cell. Tried many last cell code and cannot seen to figure this out.

 

[Joe4]

That calculation just shows you how to find that last populated row in a certain column. You would still need to incorporate it into the appropriate places in your code.

I think this is what you want (I also cleaned up a lot of your select statements). You could also incorporate it into the sorting, though I don' think it is necessary.

    Dim LastRow As Long
 
    Range("A1:H6").Delete Shift:=xlUp
    Columns("A:A").Delete Shift:=xlToLeft
    Columns("F:F").Delete Shift:=xlToLeft
    
    Columns("D:D").Copy
    Columns("E:E").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Columns("D:D").ClearContents
    
    Columns("A:A").Replace What:=";*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Columns("B:B").NumberFormat = "0"
    Columns("A:A").ColumnWidth = 15.14
    Columns("C:C").ColumnWidth = 26.71
    
    Columns("E:E").Replace What:="c", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Columns("E:E").NumberFormat = "0.00"
    Range("G2").NumberFormat = "0.00"
    Range("G2").FormulaR1C1 = "-1"
    
'   Find last row in column E
    LastRow = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Multiply all cells in column E by G2
    Range("G2").Copy
    Range("E1:E" & LastRow).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.DisplayZeros = False
    
    Columns("A:E").Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal

 

[iknowyourthere]

Wow that ran so fast and much cleaner also the end cell worked out as well
Thank you so much