Inserting text above first blank row then above third blank row etc.

[Livin404]

Greetings, I have a VBA where I'm trying to insert text into blank cells. On worksheet "Deficiencies" I have seven groups of ranges that I collected from VBA advance filter. I tried using the VBA: I will have six other macros like the one below, each with it's own message. I thought I could add "+1" after

 Range("A1:A121").Cells.SpecialCells(xlCellTypeBlanks).Row

then it became difficult trying to guess where the text will land. I thought one possibility is the word "NAME" is in Column A and above the word "Name" is a blank row which is where I need my statement inserted. This statement will merge Columns A through I with text wrap. I'm hoping this will work for after each macro by default it will go to the next blank cell above the word "NAME".

Sub Over_sixty_days_Notes()
    Dim FirstBlankRow As Long
    FirstBlankRow = Range("A1:A121").Cells.SpecialCells(xlCellTypeBlanks).Row
    Cells(FirstBlankRow, "A") = "Travelers cannot remain in the system for over 60 Days."
End Sub

 

[My Aswer Is This]

Well this should work with Range in column A
Now if you would explain where others ranges are and what value to enter in empty cells I can help you more.
You would not need 6 different scripts.

Sub Find_Empty_Cells()
'Modified  1/2/2021  9:37:49 AM  EST
Application.ScreenUpdating = False
Dim r As Range
Dim ans As String
ans = "Travelers cannot remain in the system for over 60 Days."
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For Each r In Range("A1:A" & Lastrow)
   If r.Value = "" Then r.Value = ans
Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

If you want to put the same text into all blanks cells in col A try

Sub Livin()
   With Sheets("Deficiencies").Range("A:A").SpecialCells(xlBlanks)
      .Value = "Travelers cannot remain in the system for over 60 Days."
      Intersect(.EntireRow, .Parent.Range("A:I")).HorizontalAlignment = xlCenterAcrossSelection
   End With
End Sub

Rather than merging cells A:I (which is a very bad idea) it uses center across selection instead.

 

[Livin404]

Well this should work with Range in column A
Now if you would explain where others ranges are and what value to enter in empty cells I can help you more.
You would not need 6 different scripts.

Sub Find_Empty_Cells()
'Modified  1/2/2021  9:37:49 AM  EST
Application.ScreenUpdating = False
Dim r As Range
Dim ans As String
ans = "Travelers cannot remain in the system for over 60 Days."
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For Each r In Range("A1:A" & Lastrow)
   If r.Value = "" Then r.Value = ans
Next
Application.ScreenUpdating = True
End Sub

The ranges for the other ones will be just like this one. I already have the coding for the advanced filter to copy and paste on the “deficiencies” worksheet. These ranges all run A1:I1 and the rows depends upon how many discrepancies are detected from the advanced filter. These seven groups of ranges are pasted in order on top of one another with a space between them.
The coding for these seven macros will vary slightly, so there will be “no ambiguous” errors detected.

 

[Fluff]

Do you want the same text in each blank cell, or should it be different text each time?

 

[Livin404]

Do you want the same text in each blank cell, or should it be different text each time?

Thank you for the response I provide an image of how I would hope the macro(s) would perform. The highlight is the inserted text the blank rows will remain blank. The darkened rows is the data present after I performed my advanced filter, which fortunately I managed that myself. In addition I provided a VBA which I can get it to work, but after the first one I don't want it to be a guessing game where the macro is going to place the next inserted text. I included three macros which will place inserted text, but I feel there is a much more efficient way of completing it. I thought about basing the macros around the word "Date" in Column A, it might be easier to flag the correct row to fill. They run from Column A to Column I and only the rows with the added text would be word wrap. The name of the workbook is "Deficiencies" Thank you,

Sub Over_sixty_days_Notes()
    Dim FirstBlankRow As Long
    FirstBlankRow = Range("A1:A121").Cells.SpecialCells(xlCellTypeBlanks).Row
    Cells(FirstBlankRow, "A") = "Travelers cannot remain in the system for over 60 Days."
End Sub

Sub CAT_One_Notes()
    Dim ThirdBlankRow As Long
    ThirdBlankRow = Range("A1:A121").Cells.SpecialCells(xlCellTypeBlanks).Row + 1
    Cells(ThirdBlankRow, "A") = "CAT I- Travel Type cannot be OL Keep an eye for upgrades; these travelers will not return as a CAT I."
End Sub

Sub CAT_TWO_Notes()
    Dim FifthBlankRow As Long
    FifthBlankRow = Range("A1:A121").Cells.SpecialCells(xlCellTypeBlanks).Row + 4
    Cells(FifthBlankRow, "A") = "CAT II- EML Travelers cannot travel within the USA. Dependents cannot travel unnaccompanied in CAT II."
End Sub

 

[Fluff]

I provide an image of how I would hope the macro(s) would perform.

Where?

The highlight is the inserted text the blank rows will remain blank.

If the blank rows remain blank, the why did you originally say

I have a VBA where I'm trying to insert text into blank cells.

 

[Livin404]

Do you want the same text in each blank cell, or should it be different text each time?

I don't know why it didn't attach. Thank you for getting back so soon. Oh no different text in each entry

 

[My Aswer Is This]

Now that Fluff is helping you with this question. I will move on and help someone else who has not received any help. Take care

 

[Livin404]

Where?


If the blank rows remain blank, the why did you originally say

About the blank rows there a two blank rows between each advanced filter process. The one that is not highlighted can be left blank. Sorry for the confusion.

Well this should work with Range in column A
Now if you would explain where others ranges are and what value to enter in empty cells I can help you more.
You would not need 6 different scripts.

Sub Find_Empty_Cells()
'Modified  1/2/2021  9:37:49 AM  EST
Application.ScreenUpdating = False
Dim r As Range
Dim ans As String
ans = "Travelers cannot remain in the system for over 60 Days."
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For Each r In Range("A1:A" & Lastrow)
   If r.Value = "" Then r.Value = ans
Next
Application.ScreenUpdating = True
End Sub

Thank you for the response. I did try your macro and it inserted it in each and every blank row. I'm sure it will come together in the end.