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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,005
Office Version
  1. 365
Platform
  1. Windows
You cannot align left when using center across selection.
If it has to be left aligned, I think your only option would be to merge the cells.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Livin404

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You cannot align left when using center across selection.
If it has to be left aligned, I think your only option would be to merge the cells.
Thank you, I'm really happy with the work you did. I thought I spend just a little time to make it in the perfect presentation, yet I'm here three hours later and struggling thus why I haven't closed this out. I included a portion of the macro I made changes to below. I got the Columns to merge and align left and word wrap. However, I can't get the rows to autofit the height. Also the 2ND word NAME has increased in size for I don't know why. The row right above the word NAME is fine.

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 = xlLeft
            .Range("A1:I1").Merge
            .Font.Name = "Times New Roman"
            .Font.Size = 12
            .WrapText = True
            .Font.Bold = True
            .BorderAround xlNone
            .EntireRow.AutoFit
             End With
      Next i
   End With
End Sub

Capture.JPG
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,005
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
         With Ar(i + 1).Offset(Ar(i + 1).count - 1).Resize(1)
            .Value = Ary(i)
            .HorizontalAlignment = xlLeft
            .Range("A1:I1").Merge
            .Font.Name = "Times New Roman"
            .Font.Size = 12
            .WrapText = True
            .Font.Bold = True
         End With
You cannot autofit merged cells.
 

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
Thank you, and thank you for responding to the follow-up questions. Spectacular job!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,005
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,020
Messages
5,575,610
Members
412,679
Latest member
TSpan
Top