copy and insert hidden row

[Janelle S]

I have the gotten the following VBA from previous post to insert and new line
- works great, but I want to be able to hide row (i) so people cannot insert
data into it. Is this possible? Thanks heaps in anticipation.

Public Sub ProcessData()
Const TEST_COLUMN As String = "a"
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

Application.ScreenUpdating = False
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Cells(i, TEST_COLUMN).Value = "add" Then
.Rows(i).Copy
.Rows(i + 1).Insert
.Cells(i + 1, "a").Delete
.Cells(i + 1, "b").Select
End If
Next i
End With

End Sub

 

[Dave Peterson]

Maybe...

If .Cells(i, TEST_COLUMN).Value = "add" Then
.Rows(i).Copy
.Rows(i + 1).Insert
.Cells(i + 1, "a").Delete
.Cells(i + 1, "b").Select
.Rows(i).Hidden = True
End If

 

[Janelle S]

Thanks Dave

This hides the row at the end of the command, which is great, however when I
run the macro again nothing happens - I think because the row is hidden. I
have tried putting in
..Rows(i).Hidden = False before the Copy line but this doesn't work either.

Your help is very much appreciated. J

 

[Dave Peterson]

It worked for me.

But the newly inserted row was also hidden (on the second and subsequent runs).

When I added the ".rows(i).hidden = false" line, it worked ok, too.

You may want to unhide all the rows to check your results.

ps.

Your code deletes the cell in column A and shifts the cells in column A up a
row. But columns B:xxx are in the same position.

This may be what you want, but it seems kind of weird to me.

 

[Janelle S]

You are amazing - by changing the End(xlUp). to End(x1down). it worked - if
you hadn't pointed that out, I would be stuck for hours. Thanks a bunch.

 

[Dave Peterson]

First, it's xldown (ex-ell-down), not x1down (ex-one-down).

But I don't think that this would be a fix. Maybe you changed other things,
too.

You are amazing - by changing the End(xlUp). to End(x1down). it worked - if
you hadn't pointed that out, I would be stuck for hours. Thanks a bunch.

 

[Janelle S]

Hi Dave - thanks for your replies and again without your help, I'd have been
stuck, so thank you so much.
I don't know what I did but its exactly what I wanted and it works!! The
x1Down was a typo, sorry. Hidden row at eg. row 91 is copied and pasted to
row below then row 91 is hidden again. Only inserts one row at a time
immediately below row 91 then goes to the start of the new row at the cell
"b" that I want to put data into.

Public Sub InsertServiceHours()
Const TEST_COLUMN As String = "a"
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

ActiveSheet.Unprotect
Application.ScreenUpdating = False
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlDown).Row
For i = iLastRow To 1 Step -1
If .Cells(i, TEST_COLUMN).Value = "add" Then
.Rows(i).Hidden = False
.Rows(i).Copy
.Rows(i + 1).Insert
.Cells(i + 1, "a").Delete
.Cells(i + 1, "b").Select
.Rows(i).Hidden = True
ActiveSheet.Protect
End If

Next i
End With

End Sub

 

[Dave Peterson]

Glad you got it working.

Hi Dave - thanks for your replies and again without your help, I'd have been
stuck, so thank you so much.
I don't know what I did but its exactly what I wanted and it works!! The
x1Down was a typo, sorry. Hidden row at eg. row 91 is copied and pasted to
row below then row 91 is hidden again. Only inserts one row at a time
immediately below row 91 then goes to the start of the new row at the cell
"b" that I want to put data into.

Public Sub InsertServiceHours()
Const TEST_COLUMN As String = "a"
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

ActiveSheet.Unprotect
Application.ScreenUpdating = False
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlDown).Row
For i = iLastRow To 1 Step -1
If .Cells(i, TEST_COLUMN).Value = "add" Then
.Rows(i).Hidden = False
.Rows(i).Copy
.Rows(i + 1).Insert
.Cells(i + 1, "a").Delete
.Cells(i + 1, "b").Select
.Rows(i).Hidden = True
ActiveSheet.Protect
End If

Next i
End With

End Sub