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]

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.

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.

So did my script do what you wanted? If not what did it do wrong?

 

[Livin404]

So did my script do what you wanted? If not what did it do wrong?

it added the same script in all rows. in the image I provided earlier it placed it in all the rows that were highlight in yellow. It would have gone only in the first highlighted row, the second macro would be placed in the second highlighted row and the process will repeat itself through the seven macros. Thank you

 

[My Aswer Is This]

In your original post you wanted to search Column A
Then you said you needed 7 scripts to do this job.

So are you saying you want to search columns 1 to 7

And I cannot see anything in your image that helps me.
We surely do not need 7 different scripts to do this job.
So the script will search from row(1) to last row in column for any empty cells.
Is this what you want?

 

[Livin404]

In your original post you wanted to search Column A
Then you said you needed 7 scripts to do this job.

So are you saying you want to search columns 1 to 7

And I cannot see anything in your image that helps me.
We surely do not need 7 different scripts to do this job.
So the script will search from row(1) to last row in column for any empty cells.
Is this what you want?

Yes I think in will take 7 macros to do this job. No I don’t want to search 7 Columns. I will need to merge Columns A through I for the input text and have it text wrapped. If you look at the image, the one text from each of the 7 macros will go to in order to those highlighted cells. The darkened cells is data that I’ve obscured for privacy and the blank unlighted cells I need left alone. These were cut and pasted using advance filtering.

 

[Fluff]

How about

Sub Livin()
   Dim Ary As Variant
   Dim Ar As Areas
   Dim i As Long
   
   Ary = Array("Text1", "Text2", "Text3")
   
   With Sheets("Deficiencies").Range("A:A").SpecialCells(xlBlanks)
      Set Ar = .Areas
      For i = 0 To UBound(Ary)
         With Ar(i + 1).Offset(Ar(i + 1).count - 1)
            .Resize(1).Value = Ary(i)
            .Resize(1, 9).HorizontalAlignment = xlCenterAcrossSelection
         End With
      Next i
   End With
End Sub

 

[Livin404]

How about

Sub Livin()
   Dim Ary As Variant
   Dim Ar As Areas
   Dim i As Long
  
   Ary = Array("Text1", "Text2", "Text3")
  
   With Sheets("Deficiencies").Range("A:A").SpecialCells(xlBlanks)
      Set Ar = .Areas
      For i = 0 To UBound(Ary)
         With Ar(i + 1).Offset(Ar(i + 1).count - 1)
            .Resize(1).Value = Ary(i)
            .Resize(1, 9).HorizontalAlignment = xlCenterAcrossSelection
         End With
      Next i
   End With
End Sub

Wow, that is brilliant, it places the text in the exact rows that I needed. The last piece which I'm sure has to do to the adjusting. I added a few lines to your macro, I am hoping you could quickly review, so it won't muddle up your macro. I would like to add a merge cells A through I. Naturally only for the rows we inserted for the text. Lastly if we can get the height to work with the text wrap. I've included a photo to. The rows where you see the CATEGORY I, II etc. those are just place holders. There will be far more text in those. Thank you so much.

With Sheets("Deficiencies").Range("A:A").SpecialCells(xlBlanks)
      Set Ar = .Areas
      For i = 0 To UBound(Ary)
         With Ar(i + 1).Offset(Ar(i + 1).Count - 1)
            .Resize(1).Value = Ary(i)
            .Resize(1, 9).HorizontalAlignment = xlCenterAcrossSelection
            .Font.Name = "Times New Roman"
            .Font.Size = 12
            .Font.Bold = True
            .HorizontalAlignment = xlLeft
            .BorderAround xlNone
            .VerticalAlignment = xlCenter
            .WrapText = True
         End With
      Next i
   End With
End Sub

 

[Fluff]

I would like to add a merge cells A through I.

Don't, merged cells are an abomination & should be avoided like the plague.
You can get the same effect with CenterAcrossSelection

 

[Livin404]

Don't, merged cells are an abomination & should be avoided like the plague.
You can get the same effect with CenterAcrossSelection

Got it! Is because the limitations you get when you merge cells? What about the word wrap?

 

[Fluff]

Is because the limitations you get when you merge cells?

Yes, VBA doesn't like them & a lot of formulae don't either, not to mention sorting & filtering.
To autofit the row, add this after the wraptext

            .EntireRow.AutoFit

 

[Livin404]

Yes, VBA doesn't like them & a lot of formulae don't either, not to mention sorting & filtering.
To autofit the row, add this after the wraptext

            .EntireRow.AutoFit

That definitely worked thank you, the image is the end product. What would be my best option to get all the lines to the left. Aligning left will not work. This should be the last thing now. Everything else is great.