Mod Macro: Insert Bogus Values Into Empty Cells After Data, UP TO Row 101

[jeffcoleky]

Excel 2016. Windows 10

I have an Excel macro that exports data into a file so that we can run a process on it. However the process we run the data against requires this exported file BE AT LEAST 101 rows of data for the process to work.

So I need help modifying this macro to automatically fill the remaining EMPTY rows with the bogus data below, up to line 101. Make sense?

PS. if there is no data in row A, then there won't be data in any of the other rows

Bogus data to input in empty rows up to row 101 (Col Letter):

A = "000 AAA"
B = "Bob Smith"
C = "Bob"
D = "Smith"
E = "123 Main"
F = "Smithfield"
G = "NC"
H = "50001"​

LINK to Google Spreadsheet Containing Before / After Sample Data is Here

Here's the current code to update:

Sub EXPORT_TO_MailMergeImportMe_CSV() ' Exports Mailmerge data to run Mail Merge Envelopes


  
    Application.Calculation = xlManual
    Application.DisplayAlerts = False
   
    Sheets("Mailmerge").Select
    
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    Set myrng = ActiveWindow.RangeSelection
    Set newbook = Workbooks.Add


    With newbook
    .Title = "MailMerge_ImportMe"
    .SaveAs FileName:="C:\temp\MailMerge_ImportMe.csv", FileFormat:=xlCSV
    End With
    
    myrng.copy
    Windows("MailMerge_ImportMe.csv").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Set Rangetoprocess = Range("A1:" & _
      Range("A" & Rows.Count).End(xlUp).Address)
    
    Application.ScreenUpdating = False
    
     For Each AnyCell In Rangetoprocess
       If AnyCell.Value = "" Then
       AnyCell.Value = "-"
       End If
     Next
    
    Set Rangetoprocess = Nothing
    
'// For Each row under 101 with no data in col A, FILL EMPTY ROWS WITH BOGUS DATA
'// How?
        
    Workbooks("MailMerge_ImportMe.csv").Save
    Workbooks("MailMerge_ImportMe.csv").Close
    On Error Resume Next
       
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
    Sheets("Mailmerge").Select
    Range("A2").Select
    Range("A1").Select


End Sub

Your assistance is MUCH appreciated

 

[mumps]

Try:

Sub FillBlankRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A" & LastRow + 1 & ":H101") = Array("000 AAA", "Bob Smith", "Bob Smith", "123 Main", "Smithville", "NC", "50001")
    Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Disregard

 

[Fluff]

Beaten 2it

 

[jeffcoleky]

Try:

Sub FillBlankRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A" & LastRow + 1 & ":H101") = Array("000 AAA", "Bob Smith", "Bob Smith", "123 Main", "Smithville", "NC", "50001")
    Application.ScreenUpdating = True
End Sub

Wow! That was quick and painless. Thank you! exactly what I needed.

 

[mumps]

You are very welcome.