PDA

View Full Version : Solved: Removing unwanted rows and sum



pcarmour
11-28-2012, 11:19 AM
Hi, I have a spread sheet that has between 1 and 4 empty rows after each group after sorting the data. What I am trying to do is delete the unwanted rows leaving just one empty row and in this row sum column G with the numbers from just the group above which can be any number of rows.
I have tried the following to just delete the rows but it is slow, deletes some data and stops half way through:
Range("A2").End(xlDown).Offset(1, 0).Select
Do
iMyNumber = 1 + iMyNumber
If ActiveCell.Offset(1, 0).Value = "" Then
Selection.EntireRow.Delete
Else: ActiveCell.Offset(2, 0).Select
End If
Loop Until iMyNumber = 100

I'm sure there is a better way.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

Thank you in advance.
Regards, Peter.

patel
11-28-2012, 12:39 PM
attach a sample file highlighting unwanted rows

CodeNinja
11-28-2012, 01:16 PM
pcarmour,
One of the problems I think you have is that you are deleting rows and then moving to the next item in the loop therefore, you delete row 5, increment iMyNumber to 6, and are really looking at what was row 7 because row 5 is gone... I have found it is best to work from bottom up if possible when deleting rows to avoid this problem... or you can reduce the iMyNumber by one... either works.
Try this code and see if it does what you want... I am not sure what you want with column G though... a little further explanation would help.

Thanks,

CodeNinja.

Sub test()
Dim lRow As Long 'Row counter
Dim bFirstBlank As Boolean 'used to make sure we keep an empty row

For lRow = Sheet1.Range("A65536").End(xlUp).Row To 1 Step -1 'loop through rows backwards so deleting one does not affect the count
If Sheet1.Cells(lRow, 1) <> "" Then 'row has data
bFirstBlank = True
Else 'blank row
If bFirstBlank = False Then 'we already have a blank row, so delete this row
Sheet1.Cells(lRow, 1).EntireRow.Delete
Else 'we do not yet have a blank row, so keep this row and delete future ones
bFirstBlank = False
End If
End If
Next lRow


End Sub

pcarmour
11-29-2012, 03:04 AM
Hi, That's great thank you very much. this is work ing but as the sheet grows it does get slower. With regard to adding a sum to column G please see attached.
Thanks again, Peter

pcarmour
11-29-2012, 03:07 AM
attach a sample file highlighting unwanted rows

Hi, Thank you for replying. As you can see below I have received an answer, but still looking to resolve the sum question. please see attached.
Regards,
Peter.

CodeNinja
11-29-2012, 02:32 PM
pcarmour,
I have no idea what you are trying to acomplish with the sum question. The attachment did not clear anything up for me. Please explain in detail showing how you want this calculated.

Thanks,

Codeninja.

pcarmour
12-01-2012, 01:52 PM
Hi Codeninja, Thank you very much for your expert help, I'm sorry for not being totally clear with my question. After running your code I now have multiple rows of data in varying size groups of rows with one empty/blank row between each group, I now want to sum each group in column G. in the blank row.
Hope that's clearer.
Regards,
Peter.

pcarmour
12-03-2012, 06:07 AM
Hi Codeninja,
If you have a moment can you please see if you can answer the summing question aboove. Regards, Peter.

pcarmour
12-03-2012, 06:37 AM
Hi Codeninja,
If you have a moment can you please see if you can answer the summing question aboove, I have tried to show my requirement on the attached. Regards, Peter.

CodeNinja
12-03-2012, 10:11 AM
pcarmour,
I think I understand what you want now... See if this works for you...


Dim lRow As Long
Dim bFirstBlank As Boolean
Dim dSum As Double
Dim rSum As Range
Set rSum = Range("G65536").End(xlUp).Offset(1, 0)
For lRow = Range("A65536").End(xlUp).Row To 1 Step -1 'loop through rows backwards so deleting one does not affect the count
If Cells(lRow, 1) <> "" Then 'row has data
bFirstBlank = True
If IsNumeric(Cells(lRow, 7)) Then dSum = dSum + Cells(lRow, 7)
Else 'blank row
If bFirstBlank = False Then 'we already have a blank row, so delete this row
Cells(lRow, 1).EntireRow.Delete
Else 'we do not yet have a blank row, so keep this row and delete future ones
bFirstBlank = False
'add dsum to the rsum
rSum = dSum
dsum = 0
'change rsum to this row
Set rSum = Range("G" & lRow)
End If
End If
Next lRow

pcarmour
12-03-2012, 01:05 PM
Hi CodeNinja, Thank you but I'm sorry to say it hasn't worked, please see attached example of what I need. Please note I am after the sum formula in colmn G so that if any rows are added manually they will still be added in. Regards, Peter.

CodeNinja
12-04-2012, 02:22 PM
pcarmour,
I think I understand now. Ok, try the following code. It will put the formula in instead of the sum total. To get it to work, you MUST declare rSum as a range at the top of the code... so first line of the code, put

Dim rsum As Range

Then replace the previous loop with the following code:

Set rsum = Range("G65536").End(xlUp).Offset(1, 0)
For lrow = Range("A65536").End(xlUp).Row To 1 Step -1 'loop through rows backwards so deleting one does not affect the count
If Cells(lrow, 1) <> "" Then 'row has data
bFirstBlank = True
Else 'blank row
If bFirstBlank = False Then 'we already have a blank row, so delete this row
Cells(lrow, 1).EntireRow.Delete
Else 'we do not yet have a blank row, so keep this row and delete future ones
bFirstBlank = False
'put sum in rsum cell
rsum = "=sum(G" & lrow + 1 & ":" & rsum.Offset(-1, 0).Address(False, False) & ")"
'change rsum to this row
Set rsum = Range("G" & lrow)
End If
End If
Next lrow

pcarmour
12-05-2012, 02:45 AM
FANTASTIC!!!
Thank you, absolute genius.

I'm not sure if you ever get involved in threads that others are helping with (the ethics of this forum) but if you get a chance can you please have a look at my thread: Selecting multi Rows and Colums in a List Box.

Best Regards,
Peter.