VBA to fill an existing value down until last row

Question

I already have a working macro that is transposing 5 rows into columns and append them after the last column.

The problem is that I need a long format so basically I want to automate the filling up, with the same data, until the end of data (basically the last row). I have 600 files and all these files have a different number of rows.

Sub LoopFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'your code here
                Range("A2:B6").Select
                Selection.Copy
                Range("I10").Select
                Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=True
                Rows("1:8").Select
                Application.CutCopyMode = False
                Selection.Delete Shift:=xlUp
                With Sheets("Calculated Saccades")
                    .Range("I3").AutoFill .Range("I4:I" & .Cells(.Rows.Count, "A").End(xlUp).Row)
                End With
                ActiveWorkbook.Save
                ActiveWorkbook.Close
            End With
            xFileName = Dir
        Loop
    End If
End Sub

The With Sheets is returning an error. Any ideas? Thank you so very much!

Just so you know if you write `Range("A2").Resize(10,1).Value = 1` it will fill 10 cells under `A2` with the value 1. — John Alexiou, Oct 14 '19 at 20:12
Also a naked `Range()` call (for example `Range("A2:B6").Select`) acts on `ActiveSheet` which may or maynot be the sheet you want the range on. Always be specific on what worksheet you are reading values for. VBA is far too permissive with these things and it is dangerous when you don't understand what is going on. — John Alexiou, Oct 14 '19 at 20:21
Sorry, but I am not getting the point here. The sheet is declared : With Sheets("Calculated Saccades") .Range("I3").AutoFill .Range("I4:I" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With — OviSele, Oct 15 '19 at 06:55

aucuparia · Answer 1 · 2019-10-14T15:14:10.643

1

You're already inside a With...End With block (With Workbooks.Open(xFdItem & xFileName) - this loops through workbooks if you select more than one in the File Open dialog box); you can't nest these.

You can simply replace

    With Sheets("Calculated Saccades")
  .Range("I3").AutoFill .Range("I4:I" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With

with:

    Sheets("Calculated Saccades").Range("I3").AutoFill
    Sheets("Calculated Saccades").Range("I4:I" & .Cells(.Rows.Count, "A").End(xlUp).Row)

You should also read How to avoid using Select in Excel VBA.

edited Oct 14 '19 at 15:14

answered Oct 14 '19 at 14:39

aucuparia

2,021
20
27

Thanks for the very swift response! You are absolutely correct about the With. Unfortunately, this line of code is returning an error: Runtime Error 438 Object doesn’t support this property or method I have tried to run is separately, in a single sheet, just for test, and I get Compile error, invalid or unqualified reference and .Rows is highlighted in blue. Thank you again! – OviSele Oct 14 '19 at 15:10
my bad...missed the second call to `.Range`. Corrected again. – aucuparia Oct 14 '19 at 15:14
Also, I have tried a separate macro, that I can run on all files after the initial copy/paste/transpose, to avoid the loop Still no luck. It returns an error (Argument not optional for the Autofill. Sub Test() Dim ws As Worksheet Set ws = Worksheets("Calculated Saccades") With ws Range("I3").AutoFill Range ("I4:I" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) End With 'ws.Range("I3").AutoFill 'ws.Range ("I4:I" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) End Sub – OviSele Oct 14 '19 at 18:08
`you can't nest these` - yes you can. – GSerg Oct 14 '19 at 20:03
Thank you. Anyhow, is there a simple code line(s) that allow me to fill down automatically one column, with the value from a specific cell (I3 here), until the end of data (variable number of rows)? – OviSele Oct 15 '19 at 07:02

VBA to fill an existing value down until last row

1 Answers1