VBA autofilling code

shredr

New Member
Joined
Jan 23, 2007
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Afternoon...

I have a recorded macro that pretty much does what I need, except that the files I'm manipulating have varying number of rows.
When I recorded it, I apparently had 1123 rows to deal with.
If my new files have less than that I'm OK.
But over 1123 and I have to manually do some work...who needs that??

I've been trying to be clever and have gotten it to auto select some things to the last cell in a column, but haven't been able to autofill down.

The bold line is what I'm stumbling on, the comment lines are what was running successfully before I outsmarted myself.

Thanks for any & all suggestions!


Rich (BB code):
 Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]&""-""&RC[-1]"
    Range("E2").Select
    ActiveWindow.SmallScroll ToRight:=1
    ' line below is this one with auto select down Selection.AutoFill Destination:=Range("E2:E1123")
    Selection.AutoFill Destination:=Selection.End(xlDown).Select
    'Range("E2:E1123").Select
    'Range("E2").Select
    'Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
Last edited by a moderator:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
auto select some things to the last cell in a column
I know what your code is saying but just to confirm what column is the last cell with data that you are trying to fill down to in?
 
Upvote 0
When posting vba code in the forum, please use the available code tags (SNIP is not one of them). It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time. 😊

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Bit of a guess but does this do what you want? Test with a copy of your workbook.
Generally you do not need to 'Select' cells/ranges to work with them and selecting slows your code.

VBA Code:
Sub Test()
  With Range("E2:E" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=RC[-3]&""-""&RC[-1]"
    .Offset(, -4).Value = .Value
  End With
End Sub
 
Upvote 0
I should've just put everything here to begin with...
(And updated my profile, thanks for pointing that out Peter)
I know it's clunky and inefficient, but I guess that's what happens with recorded macros...

It functions fine until I get tables longer than 1132 rows...sometimes I get over 2500 lines and it irks me that I can't figure out how to make it automatically figure out how to go to the last line on autopilot.

There's some columns repeating entries, not worried about that. What it gets pasted into afterward has the same columns, not a big deal. I'm the only one that uses it...

Thanks for the assist!

This is a dummy line of what I'm starting with

all thru july 2024 zzzzzz.XLSX
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1DOCIDDOCTYPELINENUMITEMLOCIDDATETRANTYPECOIDSALESMANCONAMECUSTPONODESTTYPEDESTIDQUANTITYOURPRICESELLUMSELLCONVLINESEQDESCRIPSTOCKUMSELLQTYUNITPRICESHIPVALPRODCLASMISCFLDMISCFLD2KEYFIELDKEYDESCTOTSHIPVAL
2123456ZZ9998768NYC4/1/2024ogoogle1237twitter888555222ar996633105225EA 199test lineEA 10522523625FG 4/1/2024google
Sheet1



And this is what I'm trying to get to

all thru july 2024 zzzzzz comverted.XLSX
ABCDEFGHIJKLMNOPQ
1SO#DOCIDDOCTYPELINENUMSO & line numberITEMDESCRIPDATECOIDCONAMECUSTPONOQUANTITYOURPRICESELLUMUNITPRICESHIPVALPRODCLAS
2123456-99123456ZZ99123456-9998768test line4/1/2024google123twitter888555222105225EA 22523625FG
Sheet1
Cell Formulas
RangeFormula
E2E2=B2&"-"&D2



Here's the code from the recorded macro that I'm trying to clean up


VBA Code:
Sub ISO_shipping_reshuffle()
'
' ISO_shipping_reshuffle Macro
' reorganizing for the ISO reject tracking
'

'
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "SO#"
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "SO & line number"
    Range("E2").Select
    Selection.End(xlDown).Select
    Range("F1123").Select
    Selection.NumberFormat = "General"
    Range("B1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B1123")
    Range("B1:B1123").Select
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=TRIM(RC[3])"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B1123")
    Range("B1:B1123").Select
    Selection.Copy
    Range("E1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]&""-""&RC[-1]"
    Range("E2").Select
    ActiveWindow.SmallScroll ToRight:=1
    Selection.AutoFill Destination:=Range("E2:E1123")
    Range("E2:E1123").Select
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("U:U").Select
    Application.CutCopyMode = False
    Selection.Cut
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("L:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("O:R").Select
    Selection.Delete Shift:=xlToLeft
    Columns("R:W").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("H4").Select
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("H1:H1123"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B2").Select
    
End Sub
 
Upvote 0
Give this version a try with a copy of your worksheet.
As well as getting rid of code not needed I have changed the order but commented to explain what is happening.
See if it produces the results you want.

BTW, for the future, just a few more rows than just one would be better for sample and expected results. ;)

VBA Code:
Sub ISO_shipping_reshuffle_v2()
  Dim lr As Long
  
  Application.ScreenUpdating = False
  lr = Range("A" & Rows.Count).End(xlUp).Row      'Find the last row
  Range("E:E,G:G,I:I,L:M,Q:R,T:U,Y:AE").Delete    'Get rid of unwanted columns
  Columns("L").Cut                  'Move DESCRIP col
  Columns("E").Insert               'Move DESCRIP col
  Columns("A:B").Insert             'Insert working columns
  Columns("F").Insert               'Insert working columns
  With Range("B1:B" & lr)                               'Use new col B as a base for cleanup
    .FormulaR1C1 = "=TRIM(RC[1])"                       'Trim new col C ..
    .Offset(, 1).Value = .Value                         '.. & put results back in col C
    .FormulaR1C1 = "=TRIM(RC[3])"                       'Trim new col E ..
    .Offset(, 3).Value = .Value                         '.. & put results back in col E
    .Offset(, 4).FormulaR1C1 = "=RC[-3]&""-""&RC[-1]"   'Put concat formula in new col F ..
    .Offset(, -1).Value = .Offset(, 4).Value            '.. & put results in new col A
    .EntireColumn.Delete                                'Remove col B helper column
  End With
  Range("A1").Value = "SO#"                             'Add new header
  Range("E1").Value = "SO & line number"                'Add new header
  With Range("A1").CurrentRegion                        'Apply borders, AutoFit, AutoFilter & Sort on Date (col H)
    .BorderAround LineStyle:=xlContinuous, Weight:=xlThin
    With .Borders(xlInsideVertical)
      .LineStyle = xlContinuous
      .Weight = xlThin
    End With
    With .Borders(xlInsideHorizontal)
      .LineStyle = xlContinuous
      .Weight = xlThin
    End With
    .EntireColumn.AutoFit
    .AutoFilter
    .Sort Key1:=.Columns("H"), Order1:=xlAscending, Header:=xlYes
  End With
  Range("B2").Select
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Holy buckets that worked faster than snapping my fingers. If you weren't on the other side of the world I'd buy you a beer!

Gonna have to study this one for some tricks.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top