Nedd Help with an input box

[Guest]

Below is a macro that I use to insert blank lines after the value in Column A
changes. I need to be able to select which column I want the macro to
evaluate. I am thinking that an input box is the easiest way, but do not know
how to do it. Thanks!

Sub InsertRows()
Dim cRows As Long
Dim i As Integer

For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A") <> Cells(i + 1, "A") Then
Cells(i + 1, "A").EntireRow.Insert
End If
Next i
End Sub

 

[Tom Ogilvy]

Sub InsertRows()
Dim i As Integer
Dim rng as Range
Dim A as Long
On Error resume Next
set rng = Application.InputBox("Select column using mouse",Type:=8)
On Error goto 0
if rng is nothing then
msgbox "No column selected - terminating"
exit sub
End if
A = rng.Column
For i = Cells(Rows.Count, A).End(xlUp).Row To 2 Step -1
If Cells(i, A) <> Cells(i + 1, A) Then
Cells(i + 1, A).EntireRow.Insert
End If
Next i
End Sub

 

[Bob Phillips]

This code will allow you to select a cell or column with a mouse and uses
that

Sub InsertRows()
Dim cRows As Long
Dim i As Integer
Dim cCol As Range

Set cCol = Application.InputBox("Select a cell or colun", Type:=8)

For i = Cells(Rows.Count, cCol.Column).End(xlUp).Row To 2 Step -1
If Cells(i, cCol.Column) <> Cells(i + 1, "A") Then
Cells(i + 1, cCol.Column).EntireRow.Insert
End If
Next i
End Sub

 

[Guest]

Thanks for the great responses. I got Tom's to work, but Bob yours seems to
be looking at all columns instead of just one. I appreciate the help!!

 

[Tom Ogilvy]

There is a typo in Bob's code:

If Cells(i, cCol.Column) <> Cells(i + 1, "A") Then

should be

If Cells(i, cCol.Column) <> Cells(i + 1, cCol.Column) Then