copy or delete rows by position

morbenforsen

New Member
Joined
Jul 4, 2015
Messages
20
i have a column (col k) which contains numbers that correspond to a certain row (not in continuous sequential order)


example values; k2=194, k3=326, k4=631, k5=1011 etc.


I want to keep only the rows in the worksheet indicated by each value (I want to keep the entire row 194, row 326, row 631 etc etc.)


It doesnt matter if I copy these to another sheet, or delete all the other rows, etc.


I am hoping to find the shortest way to accomplish this with VBA.


All help is most graciously appreciated.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
So are you saying you only want to keep the rows with some value in column "K" ??

If not what values must be in column "K" to keep this row.
 
Last edited:
Upvote 0
This will create a new sheet and put your designated rows on it in the same order they appear in column K of the active sheet.

Code:
Sub keepRows()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveSheet
Sheets.Add After:=ActiveSheet
Set sh2 = ActiveSheet
For Each c In sh1.Range("K2", sh1.Cells(Rows.Count, "K").End(xlUp))
    If c.Value > "" Then sh1.Rows(c.Value).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub
 
Upvote 0
"So are you saying you only want to keep the rows with some value in column "K" ??

If not what values must be in column "K" to keep this row."

Hi thanks for your reponse but no...that's not what I'm trying to do. please let me elaborate.

k2 has the value '194'. The value '194' happens to represent the NUMBER OF THE ROW that I want to keep. I want to keep the entire row number 194, and then also row 326 and so on.

I want to keep only the rows in the sheet, the numbers of which happen to be given by the values in column K.

Does this explanation help clarify my goal a bit better? If not let me know I'll be glad to re-explain or add a sample etc..thanks much.
 
Last edited:
Upvote 0
This will create a new sheet and put your designated rows on it in the same order they appear in column K of the active sheet.

End Sub[/CODE]

JLGWhiz thanks very much. Worked perfectly. I like idea of just moving rows of interest to a new sheet instead of deleting. Very well done thanks again.
 
Upvote 0
JLGWhiz thanks very much. Worked perfectly. I like idea of just moving rows of interest to a new sheet instead of deleting. Very well done thanks again.
You're welcome,
regards, JLG
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top