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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
345
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
Well this should work with Range in column A
Now if you would explain where others ranges are and what value to enter in empty cells I can help you more.
You would not need 6 different scripts.
VBA Code:
Sub Find_Empty_Cells()
'Modified  1/2/2021  9:37:49 AM  EST
Application.ScreenUpdating = False
Dim r As Range
Dim ans As String
ans = "Travelers cannot remain in the system for over 60 Days."
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For Each r In Range("A1:A" & Lastrow)
   If r.Value = "" Then r.Value = ans
Next
Application.ScreenUpdating = True
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
If you want to put the same text into all blanks cells in col A try
VBA Code:
Sub Livin()
   With Sheets("Deficiencies").Range("A:A").SpecialCells(xlBlanks)
      .Value = "Travelers cannot remain in the system for over 60 Days."
      Intersect(.EntireRow, .Parent.Range("A:I")).HorizontalAlignment = xlCenterAcrossSelection
   End With
End Sub
Rather than merging cells A:I (which is a very bad idea) it uses center across selection instead.
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
345
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Well this should work with Range in column A
Now if you would explain where others ranges are and what value to enter in empty cells I can help you more.
You would not need 6 different scripts.
VBA Code:
Sub Find_Empty_Cells()
'Modified  1/2/2021  9:37:49 AM  EST
Application.ScreenUpdating = False
Dim r As Range
Dim ans As String
ans = "Travelers cannot remain in the system for over 60 Days."
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For Each r In Range("A1:A" & Lastrow)
   If r.Value = "" Then r.Value = ans
Next
Application.ScreenUpdating = True
End Sub
The ranges for the other ones will be just like this one. I already have the coding for the advanced filter to copy and paste on the “deficiencies” worksheet. These ranges all run A1:I1 and the rows depends upon how many discrepancies are detected from the advanced filter. These seven groups of ranges are pasted in order on top of one another with a space between them.
The coding for these seven macros will vary slightly, so there will be “no ambiguous” errors detected.
 
Last edited:

Fluff

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

ADVERTISEMENT

Do you want the same text in each blank cell, or should it be different text each time?
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
345
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Do you want the same text in each blank cell, or should it be different text each time?
Thank you for the response I provide an image of how I would hope the macro(s) would perform. The highlight is the inserted text the blank rows will remain blank. The darkened rows is the data present after I performed my advanced filter, which fortunately I managed that myself. In addition I provided a VBA which I can get it to work, but after the first one I don't want it to be a guessing game where the macro is going to place the next inserted text. I included three macros which will place inserted text, but I feel there is a much more efficient way of completing it. I thought about basing the macros around the word "Date" in Column A, it might be easier to flag the correct row to fill. They run from Column A to Column I and only the rows with the added text would be word wrap. The name of the workbook is "Deficiencies" Thank you,

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

VBA Code:
Sub CAT_One_Notes()
    Dim ThirdBlankRow As Long
    ThirdBlankRow = Range("A1:A121").Cells.SpecialCells(xlCellTypeBlanks).Row + 1
    Cells(ThirdBlankRow, "A") = "CAT I- Travel Type cannot be OL Keep an eye for upgrades; these travelers will not return as a CAT I."
End Sub

VBA Code:
Sub CAT_TWO_Notes()
    Dim FifthBlankRow As Long
    FifthBlankRow = Range("A1:A121").Cells.SpecialCells(xlCellTypeBlanks).Row + 4
    Cells(FifthBlankRow, "A") = "CAT II- EML Travelers cannot travel within the USA. Dependents cannot travel unnaccompanied in CAT II."
End Sub
 

Fluff

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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
345
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Do you want the same text in each blank cell, or should it be different text each time?
Capture.JPG

I don't know why it didn't attach. Thank you for getting back so soon. Oh no different text in each entry
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
Now that Fluff is helping you with this question. I will move on and help someone else who has not received any help. Take care
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
345
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Where? ;)


If the blank rows remain blank, the why did you originally say
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.
Well this should work with Range in column A
Now if you would explain where others ranges are and what value to enter in empty cells I can help you more.
You would not need 6 different scripts.
VBA Code:
Sub Find_Empty_Cells()
'Modified  1/2/2021  9:37:49 AM  EST
Application.ScreenUpdating = False
Dim r As Range
Dim ans As String
ans = "Travelers cannot remain in the system for over 60 Days."
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For Each r In Range("A1:A" & Lastrow)
   If r.Value = "" Then r.Value = ans
Next
Application.ScreenUpdating = True
End Sub
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,174
Messages
5,570,684
Members
412,336
Latest member
Tiffany927
Top