First, you found that you have to change the first line to the name of the
sheet.

And if you change the second line to start in A2 (not A1), you'll avoid row 1.

with worksheets("Somesheetnamehere")
set myrng = .range("A1", .cells(.rows.count,"A").end(xlup))
end with

James C wrote:

Dave hi again had a second go using a new workbook.

The macro works fine but deletes my first row which has my column headings in.
Can you advise how to overcome this.

Thanks
--
James

"Dave Peterson" wrote:

Dim myCell as range
dim myRng as range
dim delRng as range

with worksheets("Somesheetnamehere")
set myrng = .range("A1", .cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
if lcase(left(mycell.value,1)) = lcase("u") then
'keep it
else
if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell, delrng)
end if
end if
next mycell

if delrng is nothing then
'nothing to delete
else
delrng.entirerow.delete
end if

========
Untested, uncompiled. Watch for typos.

I used column A and deleted the entire row. You may have to change the code.


James C wrote:

I have a list in a worksheet which has a column headings and in one column
contains text data. In the rows the data consists of items such E10, V19 and
U27.The rows in the list change frequenlty, as new data is added, so the
column length varies.

I want to get rid of any item that does not have the prefix U in front of it
and include this in part of an existing VBA statement.

Thanks
--
James

--

Dave Peterson
.


--

Dave Peterson