Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and insert hidden row
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and insert hidden row
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and insert hidden row
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and insert hidden row
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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and insert hidden row
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and insert hidden row
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. Janelle S wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and insert hidden row
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" wrote: 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. Janelle S wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy and insert hidden row
Glad you got it working.
Janelle S wrote: 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" wrote: 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. Janelle S wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste versus copy and insert copied cells | New Users to Excel | |||
copy/paste with hidden rows don't want hidden parts to paste | Excel Discussion (Misc queries) | |||
Copy subtotals without hidden rows | Excel Worksheet Functions | |||
Copy without Hidden Cols - How | Excel Discussion (Misc queries) | |||
copy only non hidden cells | Excel Worksheet Functions |