On Jul 19, 12:48*am, Joel wrote:
Sub ExportData()
'
' Macro1 Macro
' Macro recorded 7/18/2008 by Joel
'

'
* *LastRow =Range("D" & Rows.Count).End(xlUp).Row
* *Range("C10:C" & LastRow).Value =Range("E5").Value
* *Set SortRange =Range("C10:P" & LastRow)

* *SortRange.Sort_
* * * *Key1:=Range("F10"), _
* * * *Order1:=xlAscending, _
* * * *Key2:=Range("H10"), _
* * * *Order2:=xlDescending, _
* * * *Header:=xlGuess

* *RowCount = 10
* *Do WhileRange("F" & RowCount) < ""
* * * IfRange("F" & RowCount) = _
* * * * *Range("F" & (RowCount + 1)) Then

* * * * *'Put an x in rows to delete
* * * * *Range("IV" & RowCount) = "X"
* * * End If
* * * RowCount = RowCount + 1
* *Loop

* *'sortthe deleted Rows to top of the spreadsheet and then delete these rows
* *Set SortRange =Range("C10:IV" & LastRow)
* *SortRange.Sort_
* * * *Key1:=Range("IV10"), _
* * * *Order1:=xlDescending, _
* * * *Header:=xlGuess
* *LastRow =Range("IV" & Rows.Count).End(xlUp).Row
* *Rows("10:" & LastRow).Delete
* *Columns("IV").Delete

* *'Get Number of rows after duplicates are deleted
* *LastRow =Range("D" & Rows.Count).End(xlUp).Row
* *NewRow = LastRow + 2
* *Range("J" & NewRow) = "TOTAL"
* *Range("K" & NewRow).Formula = "=SUM(K10:K" & LastRow & ")"

End Sub



" wrote:
On Jul 18, 6:55 pm, Joel wrote:
Because you have so many lines, *it is better to mark the delete lines with
an X. *thensortthe X's to the top of the file and delete all the rows as a
single group. *this code should run in seconds instead of minutes. *I used
column IV to put the X for deletion then deleted column IV.

Sub ExportData()
'
' Macro1 Macro
' Macro recorded 7/18/2008 by Joel
'

'
* *LastRow =Range("D" & Rows.Count).End(xlUp).Row
* *Range("C10:C" & LastRow).Value =Range("E5").Value
* *Set SortRange =Range("C10:P" & LastRow)

* *SortRange.Sort_
* * * *Key1:=Range("F10"), _
* * * *Order1:=xlAscending, _
* * * *Key2:=Range("H10"), _
* * * *Order2:=xlDescending, _
* * * *Header:=xlGuess

* *RowCount = 10
* *Do WhileRange("D" & RowCount) < ""
* * * IfRange("D" & RowCount) = _
* * * * *Range("D" & (RowCount + 1)) Then

* * * * *'Put an x in rows to delete
* * * * *Range("IV" & RowCount) = "X"
* * * End If
* * * RowCount = RowCount + 1
* *Loop

* *'sortthe deleted Rows to top of the spreadsheet and then delete these rows
* *Set SortRange =Range("C10:IV" & LastRow)
* *SortRange.Sort_
* * * *Key1:=Range("IV10"), _
* * * *Order1:=xlDescending, _
* * * *Header:=xlGuess
* *LastRow =Range("IV" & Rows.Count).End(xlUp).Row
* *Rows("10:" & LastRow).Delete
* *Columns("IV").delete
End Sub

" wrote:
Hello All,
I am using Excel 2003 and have the following problem.

We export some data in excel on a regular basis (about 18000+ rows)..
Row 9 is the header row
The datarangeis C10 to P18000 +. The end row will vary.

Cell E5 contains a key word which should be utilized to paste the
RangeC10:C18000+ (all active Rows) with the Key word. *For eg, if E5
contains the text A then C10:C18000+ should display Account, if E5 = B
then C10:C18000+ should display Balance.

I wish to have a macro which should do the following:

a) *Paste the key word as described above.

b)Sortthe active datarangeC10:P18000+ using Col F (Primary Key -
Ascending) and Col H (Secondary Key – Descending).

b) After Sorting, Sum Col K (for all duplicate rows only) and Remove
Duplicate Rows using Col D to verify duplicate Rows.

Hope someone can help me

Thanks in advance

Rashid Khan- Hide quoted text -

- Show quoted text -

Hi Joel,
Thanks for your prompt reply.
I want to Sum Col K which is not done in the macro given by you.
Also Col F should be used to verify the duplicate Rows (by mistake I
mentioned Col D in my previous post)

Thanks for your help once again

Rashid Khan- Hide quoted text -

- Show quoted text -

Hi Joel,
I am sorry I think I did not explain my problem properly. Please see
below an example showing Before and After results

Before (duplicate rows in Col F with different amounts in Col K)
Col F Col K
Joe Smith $200.00
Joe Smith $300.00
Joe Smith $250.00
Kip Tucker $100.00
Kip Tucker $150.00
Kip Tucker $200.00

After (duplicate rows in Col F are deleted showing Sum of amounts in
Col K)
Joe Smith $750.00
Kip Tucker $450.00

Thanks for you help and time once again.

Rashid Khan