I have a workbook with data elements in column A to F and about 1500 rows
(will increase). Column D sometime has the value "not assigned". I would like
to get subset of the data by leaving "not assigned" rows out. I have written
code to look at each from row 1 to end to check for "not assigned" in column
D and drop that row. It work but takes a while to run and as the file will be
getting larger with time it willl take longer.

Can anyone help with more efficient code?

The way of speeding up the code is to use an auxilary column putting a
character like 0 in the new column insteading of deleting the rows one at a
time. Then sort on the new column in descending order bringing all the 0's
to the top of the sorted area. Then deleting all the rows with zeroes.

Use this code
Sub test()
'Use column K as auxilary column
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Range("A1:K" & Lastrow)
SortRange.Sort _
key1:=Range("K1"), _
order1:=xlDescending
Lastrow = Range("K" & Rows.Count).End(xlUp).Row
Rows("1:" & Lastrow).Delete

End Sub




"Prema" wrote:

I have a workbook with data elements in column A to F and about 1500 rows
(will increase). Column D sometime has the value "not assigned". I would like
to get subset of the data by leaving "not assigned" rows out. I have written
code to look at each from row 1 to end to check for "not assigned" in column
D and drop that row. It work but takes a while to run and as the file will be
getting larger with time it willl take longer.

Can anyone help with more efficient code?

Hi

I entered a formula to get 0 in K1 and copied the formula all the way down.
This is the only way I know to do without processing each row. Code for
sorting works OK but last row on K still gives me last row as before rather
than last row wich has 0. This is to do with the way I set the values in K
but don't know how to get 0 only where required and leave other cells
untouched .

Do you know how i CAN GET OVER THIS PROBLEM?

"Joel" wrote:

The way of speeding up the code is to use an auxilary column putting a
character like 0 in the new column insteading of deleting the rows one at a
time. Then sort on the new column in descending order bringing all the 0's
to the top of the sorted area. Then deleting all the rows with zeroes.

Use this code
Sub test()
'Use column K as auxilary column
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Range("A1:K" & Lastrow)
SortRange.Sort _
key1:=Range("K1"), _
order1:=xlDescending
Lastrow = Range("K" & Rows.Count).End(xlUp).Row
Rows("1:" & Lastrow).Delete

End Sub




"Prema" wrote:

I have a workbook with data elements in column A to F and about 1500 rows
(will increase). Column D sometime has the value "not assigned". I would like
to get subset of the data by leaving "not assigned" rows out. I have written
code to look at each from row 1 to end to check for "not assigned" in column
D and drop that row. It work but takes a while to run and as the file will be
getting larger with time it willl take longer.

Can anyone help with more efficient code?

You can use PasteSpecial Values to get rid of the formula

Sub test()
'Use column K as auxilary column
Columns("K").copy
Columns("K").PasteValue Paste:=xlPasteValues
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Range("A1:K" & Lastrow)
SortRange.Sort _
key1:=Range("K1"), _
order1:=xlDescending
Lastrow = Range("K" & Rows.Count).End(xlUp).Row
Rows("1:" & Lastrow).Delete

End Sub


"Prema" wrote:

Hi

I entered a formula to get 0 in K1 and copied the formula all the way down.
This is the only way I know to do without processing each row. Code for
sorting works OK but last row on K still gives me last row as before rather
than last row wich has 0. This is to do with the way I set the values in K
but don't know how to get 0 only where required and leave other cells
untouched .

Do you know how i CAN GET OVER THIS PROBLEM?

"Joel" wrote:

The way of speeding up the code is to use an auxilary column putting a
character like 0 in the new column insteading of deleting the rows one at a
time. Then sort on the new column in descending order bringing all the 0's
to the top of the sorted area. Then deleting all the rows with zeroes.

Use this code
Sub test()
'Use column K as auxilary column
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Range("A1:K" & Lastrow)
SortRange.Sort _
key1:=Range("K1"), _
order1:=xlDescending
Lastrow = Range("K" & Rows.Count).End(xlUp).Row
Rows("1:" & Lastrow).Delete

End Sub




"Prema" wrote:

I have a workbook with data elements in column A to F and about 1500 rows
(will increase). Column D sometime has the value "not assigned". I would like
to get subset of the data by leaving "not assigned" rows out. I have written
code to look at each from row 1 to end to check for "not assigned" in column
D and drop that row. It work but takes a while to run and as the file will be
getting larger with time it willl take longer.

Can anyone help with more efficient code?