Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opposite of a marco
With the help of others I have a marco that inserts cells into my worksheet.
I would also like to make a marco to remove these cells, so basically if you would use the insert marco and then use the delete marco your worksheet would be like it was untouched. This is the marco used for inserting: Sub InsertSemester() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row v = Rows(LastRow - 1).Formula Cells(LastRow - 1, "A").Resize(5, 1).EntireRow.Insert Rows(LastRow - 1) = v Rows(LastRow + 4).ClearContents Range("AU2:BG6").Copy Cells(LastRow, "A") End Sub Hope You Can Help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opposite of a marco
Sub DelLastFive()
Dim lr As Long, x As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row x = lr - 4 ActiveSheet.Rows(x & ":" & lr).Delete End Sub "JBoyer" wrote: With the help of others I have a marco that inserts cells into my worksheet. I would also like to make a marco to remove these cells, so basically if you would use the insert marco and then use the delete marco your worksheet would be like it was untouched. This is the marco used for inserting: Sub InsertSemester() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row v = Rows(LastRow - 1).Formula Cells(LastRow - 1, "A").Resize(5, 1).EntireRow.Insert Rows(LastRow - 1) = v Rows(LastRow + 4).ClearContents Range("AU2:BG6").Copy Cells(LastRow, "A") End Sub Hope You Can Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opposite of a marco
I looked at your original code again and maybe it would be better using these
variable values for x and lr. Sub DelLastFive() Dim lr As Long, x As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row x = lr - 5 ActiveSheet.Rows(x & ":" & lr -1).Delete End Sub "JLGWhiz" wrote: Sub DelLastFive() Dim lr As Long, x As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row x = lr - 4 ActiveSheet.Rows(x & ":" & lr).Delete End Sub "JBoyer" wrote: With the help of others I have a marco that inserts cells into my worksheet. I would also like to make a marco to remove these cells, so basically if you would use the insert marco and then use the delete marco your worksheet would be like it was untouched. This is the marco used for inserting: Sub InsertSemester() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row v = Rows(LastRow - 1).Formula Cells(LastRow - 1, "A").Resize(5, 1).EntireRow.Insert Rows(LastRow - 1) = v Rows(LastRow + 4).ClearContents Range("AU2:BG6").Copy Cells(LastRow, "A") End Sub Hope You Can Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opposite of a marco
The first one didn't work but the second one did the trick thanks. One
additional question though. Right now with these marcos I am inserting and deleting rows. Could I instead have a marco to just move the last row down five and paste the five new rows above it, rather than inserting rows. And vice versa with deleting, could I clear the contents in the five rows above the last row and then copy the last row up five? Hopefully you can understand what I mean. "JLGWhiz" wrote: I looked at your original code again and maybe it would be better using these variable values for x and lr. Sub DelLastFive() Dim lr As Long, x As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row x = lr - 5 ActiveSheet.Rows(x & ":" & lr -1).Delete End Sub "JLGWhiz" wrote: Sub DelLastFive() Dim lr As Long, x As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row x = lr - 4 ActiveSheet.Rows(x & ":" & lr).Delete End Sub "JBoyer" wrote: With the help of others I have a marco that inserts cells into my worksheet. I would also like to make a marco to remove these cells, so basically if you would use the insert marco and then use the delete marco your worksheet would be like it was untouched. This is the marco used for inserting: Sub InsertSemester() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row v = Rows(LastRow - 1).Formula Cells(LastRow - 1, "A").Resize(5, 1).EntireRow.Insert Rows(LastRow - 1) = v Rows(LastRow + 4).ClearContents Range("AU2:BG6").Copy Cells(LastRow, "A") End Sub Hope You Can Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opposite of a marco
I am not asking to be picky, It's just that adding and deleting rows
sometimes messes up the other data on my sheet. Again, thanks for your help. "JBoyer" wrote: The first one didn't work but the second one did the trick thanks. One additional question though. Right now with these marcos I am inserting and deleting rows. Could I instead have a marco to just move the last row down five and paste the five new rows above it, rather than inserting rows. And vice versa with deleting, could I clear the contents in the five rows above the last row and then copy the last row up five? Hopefully you can understand what I mean. "JLGWhiz" wrote: I looked at your original code again and maybe it would be better using these variable values for x and lr. Sub DelLastFive() Dim lr As Long, x As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row x = lr - 5 ActiveSheet.Rows(x & ":" & lr -1).Delete End Sub "JLGWhiz" wrote: Sub DelLastFive() Dim lr As Long, x As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row x = lr - 4 ActiveSheet.Rows(x & ":" & lr).Delete End Sub "JBoyer" wrote: With the help of others I have a marco that inserts cells into my worksheet. I would also like to make a marco to remove these cells, so basically if you would use the insert marco and then use the delete marco your worksheet would be like it was untouched. This is the marco used for inserting: Sub InsertSemester() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row v = Rows(LastRow - 1).Formula Cells(LastRow - 1, "A").Resize(5, 1).EntireRow.Insert Rows(LastRow - 1) = v Rows(LastRow + 4).ClearContents Range("AU2:BG6").Copy Cells(LastRow, "A") End Sub Hope You Can Help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opposite of a marco
What you have in that last row can make a difference in how you handle the
situation. If you have formula that have relative references, the precedents and dependents of the relative cells could get screwed up by inserting and deleting rows. It can also result in generating error messages if the formulas are looking for one data type in a location and finds another. So you have to be careful about these types of transactions. In the present case, I assumed the last row was not affected since you had previously inserted the rows and indicated that everything was OK. But the short answer to your question is that yes you can do it differently. "JBoyer" wrote: I am not asking to be picky, It's just that adding and deleting rows sometimes messes up the other data on my sheet. Again, thanks for your help. "JBoyer" wrote: The first one didn't work but the second one did the trick thanks. One additional question though. Right now with these marcos I am inserting and deleting rows. Could I instead have a marco to just move the last row down five and paste the five new rows above it, rather than inserting rows. And vice versa with deleting, could I clear the contents in the five rows above the last row and then copy the last row up five? Hopefully you can understand what I mean. "JLGWhiz" wrote: I looked at your original code again and maybe it would be better using these variable values for x and lr. Sub DelLastFive() Dim lr As Long, x As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row x = lr - 5 ActiveSheet.Rows(x & ":" & lr -1).Delete End Sub "JLGWhiz" wrote: Sub DelLastFive() Dim lr As Long, x As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row x = lr - 4 ActiveSheet.Rows(x & ":" & lr).Delete End Sub "JBoyer" wrote: With the help of others I have a marco that inserts cells into my worksheet. I would also like to make a marco to remove these cells, so basically if you would use the insert marco and then use the delete marco your worksheet would be like it was untouched. This is the marco used for inserting: Sub InsertSemester() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row v = Rows(LastRow - 1).Formula Cells(LastRow - 1, "A").Resize(5, 1).EntireRow.Insert Rows(LastRow - 1) = v Rows(LastRow + 4).ClearContents Range("AU2:BG6").Copy Cells(LastRow, "A") End Sub Hope You Can Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opposite of a marco
polo? ;-)
"JBoyer" wrote: With the help of others I have a marco that inserts cells into my worksheet. I would also like to make a marco to remove these cells, so basically if you would use the insert marco and then use the delete marco your worksheet would be like it was untouched. This is the marco used for inserting: Sub InsertSemester() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row v = Rows(LastRow - 1).Formula Cells(LastRow - 1, "A").Resize(5, 1).EntireRow.Insert Rows(LastRow - 1) = v Rows(LastRow + 4).ClearContents Range("AU2:BG6").Copy Cells(LastRow, "A") End Sub Hope You Can Help |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opposite of a marco
How about NOT savingclosing. -- Don Guillett Microsoft MVP Excel SalesAid Software "Matt S" wrote in message ... polo? ;-) "JBoyer" wrote: With the help of others I have a marco that inserts cells into my worksheet. I would also like to make a marco to remove these cells, so basically if you would use the insert marco and then use the delete marco your worksheet would be like it was untouched. This is the marco used for inserting: Sub InsertSemester() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row v = Rows(LastRow - 1).Formula Cells(LastRow - 1, "A").Resize(5, 1).EntireRow.Insert Rows(LastRow - 1) = v Rows(LastRow + 4).ClearContents Range("AU2:BG6").Copy Cells(LastRow, "A") End Sub Hope You Can Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opposite of Vlookup | Excel Discussion (Misc queries) | |||
Do the opposite of this formula: | Excel Discussion (Misc queries) | |||
Now I want the opposite | Excel Programming | |||
Opposite of Concatenate | Excel Worksheet Functions | |||
opposite of IsNumeric | Excel Programming |