| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filling in blank rows
I posted this originally on the 15th, but only received one response and Im
hoping for a little further information. Im moving data from one page to another (moving inactive data from the Active Page to the History Page). Since moving data is on a line by line basis, the Active Page will contain several blank rows after other rows have been removed (transferred to the History Page). Im wondering if there is a way to step up the remaining rows (that contain data) in the Active Page without compromising the original Macros settings. Below is the Macro I am using to move the data from the Active Page to the History. Ive provided just 2 lines of the code, but I plan to continue this same format for about 30 lines of data. Thank you for your review and any further help would be greatly appreciated Jenny B. __________________________________________________ _______________________-- Private Sub Worksheet_SelectionChange (ByVal Target As Range) Application.ScreenUpdating = False If Not Intersect(Target, Range("b3")) Is Nothing Then Sheets("History").Range("a3:x3").Insert shift:=xlDown Range("d3:aa3").Copy Destination:=Sheets("History").Range("a3:x3") Range("C3:AB3").Select Range("AB3").Activate Selection.ClearContents Range("c3").Select Application.ScreenUpdating = True ElseIf Not Intersect(Target, Range("b4")) Is Nothing Then Application.ScreenUpdating = False Sheets("History").Range("a3:x3").Insert shift:=xlDown Range("d4:aa4").Copy Destination:=Sheets("History").Range("a3:x3") Range("C4:AB4").Select Range("AB4").Activate Selection.ClearContents Range("c4").Select Application.ScreenUpdating = True End If End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filling in blank rows
Apart from the fact that you can shorten your code a lot by utilising a loop,
why not just delete the moved rows, instead of clearing the data and leaving empty rows? -- Hth Kassie Kasselman "Jenny B." wrote: I posted this originally on the 15th, but only received one response and Im hoping for a little further information. Im moving data from one page to another (moving inactive data from the Active Page to the History Page). Since moving data is on a line by line basis, the Active Page will contain several blank rows after other rows have been removed (transferred to the History Page). Im wondering if there is a way to step up the remaining rows (that contain data) in the Active Page without compromising the original Macros settings. Below is the Macro I am using to move the data from the Active Page to the History. Ive provided just 2 lines of the code, but I plan to continue this same format for about 30 lines of data. Thank you for your review and any further help would be greatly appreciated Jenny B. __________________________________________________ _______________________-- Private Sub Worksheet_SelectionChange (ByVal Target As Range) Application.ScreenUpdating = False If Not Intersect(Target, Range("b3")) Is Nothing Then Sheets("History").Range("a3:x3").Insert shift:=xlDown Range("d3:aa3").Copy Destination:=Sheets("History").Range("a3:x3") Range("C3:AB3").Select Range("AB3").Activate Selection.ClearContents Range("c3").Select Application.ScreenUpdating = True ElseIf Not Intersect(Target, Range("b4")) Is Nothing Then Application.ScreenUpdating = False Sheets("History").Range("a3:x3").Insert shift:=xlDown Range("d4:aa4").Copy Destination:=Sheets("History").Range("a3:x3") Range("C4:AB4").Select Range("AB4").Activate Selection.ClearContents Range("c4").Select Application.ScreenUpdating = True End If End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filling in blank rows
Because I also have formulas at the bottom of the Active Page that total
(sum) the data that's currently left on the screen. If you Delete the Cells (or cut and paste to the other screen), this will throw off the formulas and then display an Error Message since the line values they were originally totalling have been removed. That's why I use Copy/Paste and Clear. It does not disturb the existing formulas used in that particular worksheet. Thanks for the thoughts - Jenny B. "kassie" wrote: Apart from the fact that you can shorten your code a lot by utilising a loop, why not just delete the moved rows, instead of clearing the data and leaving empty rows? -- Hth Kassie Kasselman "Jenny B." wrote: I posted this originally on the 15th, but only received one response and Im hoping for a little further information. Im moving data from one page to another (moving inactive data from the Active Page to the History Page). Since moving data is on a line by line basis, the Active Page will contain several blank rows after other rows have been removed (transferred to the History Page). Im wondering if there is a way to step up the remaining rows (that contain data) in the Active Page without compromising the original Macros settings. Below is the Macro I am using to move the data from the Active Page to the History. Ive provided just 2 lines of the code, but I plan to continue this same format for about 30 lines of data. Thank you for your review and any further help would be greatly appreciated Jenny B. __________________________________________________ _______________________-- Private Sub Worksheet_SelectionChange (ByVal Target As Range) Application.ScreenUpdating = False If Not Intersect(Target, Range("b3")) Is Nothing Then Sheets("History").Range("a3:x3").Insert shift:=xlDown Range("d3:aa3").Copy Destination:=Sheets("History").Range("a3:x3") Range("C3:AB3").Select Range("AB3").Activate Selection.ClearContents Range("c3").Select Application.ScreenUpdating = True ElseIf Not Intersect(Target, Range("b4")) Is Nothing Then Application.ScreenUpdating = False Sheets("History").Range("a3:x3").Insert shift:=xlDown Range("d4:aa4").Copy Destination:=Sheets("History").Range("a3:x3") Range("C4:AB4").Select Range("AB4").Activate Selection.ClearContents Range("c4").Select Application.ScreenUpdating = True End If End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filling in blank rows
Not clear what you mean, but how about hiding the empty rows?
I would still suggest that you should make use of a loop. It is a lot faster, a lot less coding te replicate, and a lot less chance for errors? -- Hth Kassie Kasselman "Jenny B." wrote: Because I also have formulas at the bottom of the Active Page that total (sum) the data that's currently left on the screen. If you Delete the Cells (or cut and paste to the other screen), this will throw off the formulas and then display an Error Message since the line values they were originally totalling have been removed. That's why I use Copy/Paste and Clear. It does not disturb the existing formulas used in that particular worksheet. Thanks for the thoughts - Jenny B. "kassie" wrote: Apart from the fact that you can shorten your code a lot by utilising a loop, why not just delete the moved rows, instead of clearing the data and leaving empty rows? -- Hth Kassie Kasselman "Jenny B." wrote: I posted this originally on the 15th, but only received one response and Im hoping for a little further information. Im moving data from one page to another (moving inactive data from the Active Page to the History Page). Since moving data is on a line by line basis, the Active Page will contain several blank rows after other rows have been removed (transferred to the History Page). Im wondering if there is a way to step up the remaining rows (that contain data) in the Active Page without compromising the original Macros settings. Below is the Macro I am using to move the data from the Active Page to the History. Ive provided just 2 lines of the code, but I plan to continue this same format for about 30 lines of data. Thank you for your review and any further help would be greatly appreciated Jenny B. __________________________________________________ _______________________-- Private Sub Worksheet_SelectionChange (ByVal Target As Range) Application.ScreenUpdating = False If Not Intersect(Target, Range("b3")) Is Nothing Then Sheets("History").Range("a3:x3").Insert shift:=xlDown Range("d3:aa3").Copy Destination:=Sheets("History").Range("a3:x3") Range("C3:AB3").Select Range("AB3").Activate Selection.ClearContents Range("c3").Select Application.ScreenUpdating = True ElseIf Not Intersect(Target, Range("b4")) Is Nothing Then Application.ScreenUpdating = False Sheets("History").Range("a3:x3").Insert shift:=xlDown Range("d4:aa4").Copy Destination:=Sheets("History").Range("a3:x3") Range("C4:AB4").Select Range("AB4").Activate Selection.ClearContents Range("c4").Select Application.ScreenUpdating = True End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| delte multiple blank rows in excel | Excel Worksheet Functions | |||
| blank rows between copied rows | Excel Discussion (Misc queries) | |||
| Listing data without blank rows | Excel Worksheet Functions | |||
| Insert blank rows repeatedly between every data row in Excel | Excel Discussion (Misc queries) | |||
| Sort Macro to Exclude Blank Rows? | Excel Worksheet Functions | |||
Linear Mode
