CODEWITHSUNDEEP
excelvba
lakeek
lakeek

Reputation: 25

Excel VBA not auto filling entire column

Below is a macro I created in Excel. If you look at the lines with code such as: "Selection.AutoFill Destination:=Range("V2:V" & Range("E" & Rows.Count).End(xlUp).Row)". Depending on the number of rows in the csv file it does not always autofill to the end of column on the sheet. It seems to work better when there are a lot of rows. If there are less than 10 it does not autofill to the end. The failure is very inconsistent. I am still trying to get a proper pattern of failure. Is there a better to select the range? The number of rows is never the same.

Thanks

Sub StudPrepAccounts()
'
' StudPrepAccounts Macro
'
'
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "Username"
    Range("V2").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(LEFT(RC[-19],1),LEFT(RC[-18],4),RIGHT(RC[-14],4))"
     Range("V2").Select
    Selection.AutoFill Destination:=Range("V2:V" & Range("E" & 
Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("V2:V9").Select
Range("W1").Select
ActiveWindow.ScrollColumn = 10
Range("W1").Select
ActiveCell.FormulaR1C1 = "Password"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""Ball"",RIGHT(RC[-15],4),""!"")"
Range("W2").Select
Selection.AutoFill Destination:=Range("W2:W" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("W2:W9").Select
Range("X1").Select
ActiveCell.FormulaR1C1 = "OU"
Range("X2").Select
ActiveCell.FormulaR1C1 = "OU=Students,OU=In,OU=Staging,dc=FAKECO,dc=on,dc=ca"
Range("X2").Select
Selection.AutoFill Destination:=Range("X2:X" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("X2:X9").Select
Columns("X:X").EntireColumn.AutoFit
Range("Y1").Select
ActiveCell.FormulaR1C1 = "UPN"
Range("Y2").Select
ActiveCell.FormulaR1C1 = _
    "=CONCATENATE(LEFT(RC[-22],1),LEFT(RC[-21],4),RIGHT(RC[-17],4),""@FAKECO.on.ca"")"
Range("Y2").Select
Selection.AutoFill Destination:=Range("Y2:Y" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("Y2:Y9").Select
Columns("V:Z").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
    Columns("V:V").Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
 Columns("Y:Y").Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
Application.DisplayAlerts = False
ChDir "C:\SDK"
ActiveWorkbook.SaveAs Filename:="C:\SDK\FAKECO_MIM_Export.xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close

End Sub

Upvotes: 0

Views: 83

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

Something like this?

Public Sub StudPrepAccounts()

    'With ThisWorkbook.Worksheets("Sheet1")
    'With ActiveWorkbook.Worksheets("Sheet1")
    With Workbooks("SomeWorkbook.xlsx").Worksheets("Sheet1")
    
        'Use column E to find the last row.
        Dim LastRow As Long
        LastRow = .Cells(.Rows.Count, 5).End(xlUp).Row
    
        .Range("V1") = "Username"
        .Range("V2", .Cells(LastRow, 22)).FormulaR1C1 = _
            "=CONCATENATE(LEFT(RC[-19],1),LEFT(RC[-18],4),RIGHT(RC[-14],4))"
        
        .Range("W1") = "Password"
        .Range("W2", .Cells(LastRow, 23)).FormulaR1C1 = _
            "=CONCATENATE(""Ball"",RIGHT(RC[-15],4),""!"")"
        
        .Range("X1") = "OU"
        .Range("X2", .Cells(LastRow, 24)) = "OU=Students,OU=In,OU=Staging,dc=FAKECO,dc=on,dc=ca"
        
        .Range("Y1") = "UPN"
        .Range("Y2", .Cells(LastRow, 25)).FormulaR1C1 = _
            "=CONCATENATE(LEFT(RC[-22],1),LEFT(RC[-21],4),RIGHT(RC[-17],4),""@FAKECO.on.ca"")"
            
        'Replace formula with the value in the cell.
        .Range("V2", .Cells(LastRow, 25)).Value = .Range("V2", .Cells(LastRow, 25)).Value
        
        .Columns.AutoFit
        
        'As the With statement is looking at the worksheet we can use the parent
        'of the worksheet which is the workbook.
        .Parent.Close SaveChanges:=True, _
                      Filename:="C:\SDK\FAKECO_MIM_Export.xlsx"
         
    End With

End Sub

Further reading: With statement

Upvotes: 0

Related Questions