VBA autofilling code

[shredr]

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!

 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

 

[MARK858]

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?

 

[Peter_SSs]

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.

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

 

[shredr]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	DOCID	DOCTYPE	LINENUM	ITEM	LOCID	DATE	TRANTYPE	COID	SALESMAN	CONAME	CUSTPONO	DESTTYPE	DESTID	QUANTITY	OURPRICE	SELLUM	SELLCONV	LINESEQ	DESCRIP	STOCKUM	SELLQTY	UNITPRICE	SHIPVAL	PRODCLAS	MISCFLD	MISCFLD2	KEYFIELD	KEYDESC	TOTSHIPVAL
2	123456	ZZ	99	98768	NYC	4/1/2024	o	google123	7	twitter	888555222	ar	996633	105	225	EA	1	99	test line	EA	105	225	23625	FG			4/1/2024	google
Sheet1

And this is what I'm trying to get to

all thru july 2024 zzzzzz comverted.XLSX
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	SO#	DOCID	DOCTYPE	LINENUM	SO & line number	ITEM	DESCRIP	DATE	COID	CONAME	CUSTPONO	QUANTITY	OURPRICE	SELLUM	UNITPRICE	SHIPVAL	PRODCLAS
2	123456-99	123456	ZZ	99	123456-99	98768	test line	4/1/2024	google123	twitter	888555222	105	225	EA	225	23625	FG
Sheet1

Cell Formulas
Range	Formula
E2	E2	=B2&"-"&D2

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

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

 

[Peter_SSs]

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.

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

 

[shredr]

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!

 

[Peter_SSs]

You’re welcome.