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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Excel Formula:
 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".

VBA Code:
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-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Livin404

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,008
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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
 
Solution

Livin404

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
How about
VBA Code:
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.

VBA Code:
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

Capture.JPG
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,008
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,008
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
            .EntireRow.AutoFit
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
VBA Code:
            .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.

Left Align.JPG
 

Watch MrExcel Video

Forum statistics

Threads
1,119,024
Messages
5,575,639
Members
412,680
Latest member
mbirch
Top