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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
365
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
53,139
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Livin404

Active Member
Joined
Jan 7, 2019
Messages
365
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
53,139
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
365
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
53,139
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,984
Messages
5,599,194
Members
414,296
Latest member
nachname

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top