rhadash
asked on
VBA Excel to select and move rows to new worksheet
Hi Experts,
I am trying to select rows where country_code = US and dl_state is blank then move them to a new worksheet (cut and paste).
Attached is dummy data. I have the below code that works to delete the rows when dl_state is populate with something but nothing works for blank(its blank no null).
Dim startrow As Long
'MODIFY YOUR START ROW HERE
startrow = 1
Cells(startrow, 1).Select
Do While ActiveCell.Value <> ""
If Cells(startrow, 23).Value = "US" And Cells(startrow, 25).Value = " " Then
rows(startrow).Delete (xlUp)
'Don't Modify Start Row here because the rows just moved up
Else
startrow = startrow + 1
End If
Loop
I am open to any suggestions to get this to work. Thanks.
DataTest.xls
I am trying to select rows where country_code = US and dl_state is blank then move them to a new worksheet (cut and paste).
Attached is dummy data. I have the below code that works to delete the rows when dl_state is populate with something but nothing works for blank(its blank no null).
Dim startrow As Long
'MODIFY YOUR START ROW HERE
startrow = 1
Cells(startrow, 1).Select
Do While ActiveCell.Value <> ""
If Cells(startrow, 23).Value = "US" And Cells(startrow, 25).Value = " " Then
rows(startrow).Delete (xlUp)
'Don't Modify Start Row here because the rows just moved up
Else
startrow = startrow + 1
End If
Loop
I am open to any suggestions to get this to work. Thanks.
DataTest.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Instead of Auto Filter, you could use the Advanced Filter function using the Copy to another location option.
Couple of quirks with Advanced Filter
1) If the destination for the filtered data is another sheet, before running the Advanced Filter Wizard, select a blank area on the destination sheet.
2) Copying to another location will overwrite formulas with values, this also applies to Auto Filter.
3) Copying to another location will overwrite results of previous filter results if present but only to the extent required for the new filter. If the new filter gives a smaller result set than previous filter, there may still be results from previous filter at bottome of list.
If you want more advice on Advanced FIlter, let us know. Running an Advanced Filter can be automated into VBA if so required.
Thanks
Rob H
Couple of quirks with Advanced Filter
1) If the destination for the filtered data is another sheet, before running the Advanced Filter Wizard, select a blank area on the destination sheet.
2) Copying to another location will overwrite formulas with values, this also applies to Auto Filter.
3) Copying to another location will overwrite results of previous filter results if present but only to the extent required for the new filter. If the new filter gives a smaller result set than previous filter, there may still be results from previous filter at bottome of list.
If you want more advice on Advanced FIlter, let us know. Running an Advanced Filter can be automated into VBA if so required.
Thanks
Rob H
ASKER
Thank you!
with
Open in new window
you are not testing if it is blank but whether there is a spacepls try
Open in new window
Regards