VBA to insert a blank row

kenpcli

Board Regular
Joined
Oct 24, 2017
Messages
129
I need to insert a blank column, then a blank row after a5 and insert a header with the following data:

A5= Merge Cells, B5=Site, C5=Ins. Co., D5= Chg Amt, E5= Pay Amt, F5= Adj Amt, G5=Ref Amt, H5= Bal Amt, I5=Amount Billed, j5= CA%
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
How about
Code:
Sub AddRow()

   Columns(1).Insert
   Rows(6).Insert
   Range("A5:C5").Value = Array("MergeCells", "Site", "Ins")
   Range("I5:J5").Value = Array("Amount Billed", "CA% ")
   
End Sub
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
For reference, you can add this to the other 2 subs I've supplied like
Code:
Sub kenpcli()

   Dim Ar As Areas
   Dim Rng As Range
   Dim ValU As Long
   
   With Sheets("Analysis")
      Set Ar = .Range("A6:A" & .Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
      For Each Rng In Ar
         Rng.Value = Evaluate("VLookup(" & Rng.Offset(-1).Resize(1).Address & ",'Legend Site'!A1:B1000, 2, False)")
      Next Rng
   End With
   
   Columns(1).Insert
   Rows(6).Insert
   Range("A5:C5").Value = Array("MergeCells", "Site", "Ins")
   Range("I5:J5").Value = Array("Amount Billed", "CA% ")
   
   With Columns(3)
      .Replace "Totals For SELF PAY", True, xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlConstants, xlLogical).Areas
      .Replace True, "Totals For SELF PAY", xlWhole, , False, , False, False
   End With
   For Each Rng In Ar
      Rng.EntireRow.Copy Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
      Rng.EntireRow.Delete
   Next Rng

End Sub
 
Upvote 0
it doesn't like
Set Ar = .Range("A6:A" & .Range("C" & rows.Count).End(xlUp).row).SpecialCells(xlBlanks).Areas

it says Expected Function or variable.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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