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. *then sort the 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 While Range("D" & RowCount) < ""
* * * If Range("D" & RowCount) = _
* * * * *Range("D" & (RowCount + 1)) Then

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

* *'sort the 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 data range is C10 to P18000 +. The end row will vary.

Cell E5 contains a key word which should be utilized to paste the
Range C10: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) Sort the active data range C10: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