Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Rows
I was reading about deleting blank rows but they aren't really
addressing the problem that I am having. I pull info from an outside program and insert it into an excel sheet to reformat it. What I need to do more efficiently is get rid of the blank rows across the columns, BUT not delete rows that have an info in them. For Instance... Columns A through G have info on the first row. Columns D through G have info on the second and third row Column G has info in the 4th. Between the three rows there are blank rows that I don't need and the other thing is I have about 17 pages of the same dilemma so it isn't just one set and one other thing is I run this report every other week or so and I have another that I am running weekly that can range from 5 pages to the most recent one which was 32. There are no calculations anywhere in the spreadsheet so deleting rows shouldn't be a problem. I am new in this position and have no one to ask about this type of problem. If I can cut this process time down it would be very impressive and make my life easier. Thanks!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Rows
On Mar 15, 1:25 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Try this macro which will delete any row that is completelyblank. Sub DeleteEmptyRows() 'only if entire row isblank LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete End If Next r End Sub If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben MS Excel MVP On 15 Mar 2007 12:09:40 -0700, wrote: I was reading about deletingblankrowsbut they aren't really addressing the problem that I am having. I pull info from an outside program and insert it into an excel sheet to reformat it. What I need to do more efficiently is get rid of the blankrowsacross the columns, BUT not deleterowsthat have an info in them. For Instance... Columns A through G have info on the first row. Columns D through G have info on the second and third row Column G has info in the 4th. Between the threerowsthere areblankrowsthat I don't need and the other thing is I have about 17 pages of the same dilemma so it isn't just one set and one other thing is I run this report every other week or so and I have another that I am running weekly that can range from 5 pages to the most recent one which was 32. There are no calculations anywhere in the spreadsheet so deletingrowsshouldn't be a problem. I am new in this position and have no one to ask about this type of problem. If I can cut this process time down it would be very impressive and make my life easier. Thanks!!! I will definitely try this!! BUT I found another little thing that could be a time-saver if there was a way to do it. After separating types of product so that I can see what I have and don't have, I put each list into a particular order. What I need to do with this is insert a row, fill it with black, and resize it to 3pt. This isn't the same one as what I asked about before. I have written a macro to do all the basic formatting but this action doesn't seem to want to work (in a separate macro.) because it wants to insert the row in the same row. I really would like just to be able do a shortcut to accomplish this instead of inserting, coloring, rezising then on to the next one to do exactly the same thing. Thanks again!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Rows
What would distinguish the end of one set from the next set?
This macro looks at Column A and at every change in value inserts a row colored black and set to 3pt. Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) < Cells(i, 1) Then _ Cells(i, 1).Resize(1, 1).EntireRow.Insert With Rows(i) .RowHeight = 3 .Interior.ColorIndex = 1 End With Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord On 16 Mar 2007 12:53:42 -0700, wrote: I will definitely try this!! BUT I found another little thing that could be a time-saver if there was a way to do it. After separating types of product so that I can see what I have and don't have, I put each list into a particular order. What I need to do with this is insert a row, fill it with black, and resize it to 3pt. This isn't the same one as what I asked about before. I have written a macro to do all the basic formatting but this action doesn't seem to want to work (in a separate macro.) because it wants to insert the row in the same row. I really would like just to be able do a shortcut to accomplish this instead of inserting, coloring, rezising then on to the next one to do exactly the same thing. Thanks again!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
Get number of rows that data uses, including blank rows | Excel Discussion (Misc queries) | |||
Inserting Blank rows after every row upto 2500 rows | Excel Worksheet Functions | |||
Get number of rows that data takes up, including blank rows | Excel Worksheet Functions |