VBA Blank Row insert

Jokky

New Member
Joined
Jan 23, 2014
Messages
4
Good Morning Everyone. I am looking for VBA code that will insert a blank row after every data change in row CD, but must have a message box to ask me at what row must it start.
I have the following VBA code that I use, I found in MrExcel but it has the row where to start build in to it. I need to be able to choose what row to begin because the amount of rows keeps on changing.

Sub InsertRowsAtValueChangeColumnB()
Dim X As Long, LastRow As Long
Const DataCol As String = "CD"
Const StartRow = 111
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow + 1 Step -1
If Cells(X, DataCol).Value <> Cells(X - 1, DataCol) Then Rows(X).Insert
Next
Application.ScreenUpdating = True
End Sub

Thank you in advance to anyone that can help.

Regards
Jokky

An added bonuse would be if i could colour those blank cells black from column A to CD as i use them as a visual seperator.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
792
Office Version
  1. 2013
Platform
  1. Windows
Hi
Replace
Code:
Const StartRow = 111
With
VBA Code:
Dim StartRow As Long
   StartRow = InputBox("Enter Start Row")
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
792
Office Version
  1. 2013
Platform
  1. Windows
You are welcome
Glad I helped
Thank you for the feedback
Be happy
 

Watch MrExcel Video

Forum statistics

Threads
1,118,525
Messages
5,572,641
Members
412,478
Latest member
MakeItWorkVBA
Top