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

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
743
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
 
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.
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top