If you have a lot of data maybe you hit the limit ?

See this page for a example that count the areas
http://www.rondebruin.nl/specialcells.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dave Peterson" wrote in message ...
Are the cells in column A really empty or do they contain formulas that look
empty?

If they're really empty:

on error resume next 'just in case
worksheets("somesheetnamehere").columns(1) _
.cells.specialcells(xlcelltypeblanks).entirerow.de lete
on error goto 0



Tasha wrote:

wow, thanks Dave, I didn't know that....I will try it as soon as I get my
morning reports out. Is there a way I can get rid of the rows that have
blanks in column A?

"Dave Peterson" wrote:

The asterisk (*) represents a wildcard and excel's find will use that asterisk
as a wildcard.

But if you want the actual character, you can use ~* to really find the
asterisk.

~* to find *
~? to find ?
~~ to find ~

(The tilde (~) is the "escape" character that tells excel that you really want
to find that next character--not to use it as a wildcard.

So you could change what you're looking for:

myStrings = Array("* DE", "* FI")
to
myStrings = Array("~* DE", "~* FI")

or you could have your code do it:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim FoundCell As Range
Dim wks As Worksheet
Dim myStrings As Variant
Dim iCtr As Long
Dim myRealStr As String

myStrings = Array("* DE", "* FI")

Set wks = ActiveSheet

With wks
'this line changed, too!
'columns A and K, not A:K, right?
Set myRng = Union(.Range("a3:a" & .Rows.Count), _
.Range("k3:k" & .Rows.Count))
End With

For iCtr = LBound(myStrings) To UBound(myStrings)
Do
With myRng
myRealStr = Replace(myStrings(iCtr), "~", "~~")
myRealStr = Replace(myRealStr, "*", "~*")
myRealStr = Replace(myRealStr, "?", "~?")

Set FoundCell = .Cells.Find(what:=myRealStr, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
End With
Loop
Next iCtr
End Sub



Tasha wrote:

ok, now I have a different question, but along the same lines. I have
another report that has the same data as before, ("D", "GL", "NO", "REPO" and
"RUN"), however in addition to that, have some others, such as "* DE", "* FI"
and some that are blank. I toyed with this trying to get it to remove the
rows as well, that contained this data, the problem now is some of it goes
through to column K, for instance the "* DE" may show up in Column K as well
as A. How would I do that?....wondering if the * is what was causing me to
not be able to do this, but it is part of the text field....also got stumped
on the blank cells.....If Cells in column A are blank, would need to get rid
of them as well, they would be followed by garbage in the cells in that row.
I spent about 6 hrs yesterday working on this to no avail.....help
please!!!???? Thanks in advance for all your terrific advice, you guys have
been a godsend to me!!!

"Dave Peterson" wrote:

One way:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim FoundCell As Range
Dim wks As Worksheet
Dim myStrings As Variant
Dim iCtr As Long

myStrings = Array("D", "GL", "NO", "REPO", "RUN")

Set wks = ActiveSheet

With wks
Set myRng = .Range("a3:a" & .Rows.Count)
End With

For iCtr = LBound(myStrings) To UBound(myStrings)
Do
With myRng
Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
End With
Loop
Next iCtr
End Sub



Tasha wrote:

I need a macro to find data in cell column 1, starting in cell A3 that
contains the data "D", "GL", "NO", "REPO" and "RUN". I need it to find these
cells, and if that cell contains any of the noted data above, to delete the
entire row. Can someone help me with this? I've tried using some of the
macros mentioned in this forum, but was only for one variable, and I wasn't
sure how to do it with multiple variables????

Any help appreciated!!!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson