delete or remove /filter rows using a criteria

maybe something like this, assuming your data starts in cell A1

sub cleanup

with cells(1,"A").currentregion
.autofilter
.autofilter field:=1,criteria1:="="
.offset(1).specialcells(xlcelltypevisible).entirerow.delete
.autofilter
end with
end sub

Select allOpen in new window

Rows A1... across  are headers

is there a manual way to do it?

sure

Data \ Filter
select blanks in the A1 dropdown
select the selected rows
right-click \ delete or Ctrl -

Done

the cleanup code did'nt work.
error in the last line....offset(1).specialcells(xlcelltypevisible).entirerow.delete

how do I ...
select the selected rows
right-click \ delete or Ctrl

Before you filter, as I said, take note of how many rows of data you have. (lets say 25)

Once you filter, click on top of the first row number that appears under your heading

Lets assume this is "2". Remember you have to click on top of row number, then draw down to row 26 (which is 1 more than your data range)

Then right click on top of the same row numbers and click delete. Unfilter

code works very well for me, do you have a protected sheet or merged cells maybe?

check out this screencast for video demo

Thomas

see picture. Hover your mouse over the row number as noted and the click and drag down to the your last data range to select
Temp.PNG

so the what remains are the data that's relevant .can I save it to a new workbook?

well it doesnt seem to work. I take back

what I did
 data/filter
righ click on arrow
unselect all rows
slect blanks
execute

results are all the rows appeared blank??

Yes, it is safe to save it as new workbook.

it worked! I missed the unfilter step! great thanks