It's been a while since I've done much VBA with Excel, so I'm a little rusty...

I'm working on a macro to do some formatting for me. I'm pulling in a file
from another program, into Excel. I want to Search all of Column B for a
string of all *'s. I've run a few tests with pulling in the data. And there
will only ever be one cell in the column that is all *'s. The tricky part
though, is that the actual number of *'s may vary from instance to instance.

So I'm looking for a way to search the column for a cell with a variable
number of *'s, but only *'s (some cells may have *'s and text); so that I can
then remove all rows from that row up.

Anyone have any thoughts to help get me started on this...?

Sub stary()
Dim r As Range
For Each r In Selection
If IsEmpty(r.Value) Then
Else
If WorksheetFunction.Substitute(r.Value, "*", "") = "" Then
MsgBox (r.Address & " has only stars")
End If
End If
Next
End Sub

Select some cells and the macro will tell you which have only stars in them.
--
Gary's Student


"RSteph" wrote:

It's been a while since I've done much VBA with Excel, so I'm a little rusty...

I'm working on a macro to do some formatting for me. I'm pulling in a file
from another program, into Excel. I want to Search all of Column B for a
string of all *'s. I've run a few tests with pulling in the data. And there
will only ever be one cell in the column that is all *'s. The tricky part
though, is that the actual number of *'s may vary from instance to instance.

So I'm looking for a way to search the column for a cell with a variable
number of *'s, but only *'s (some cells may have *'s and text); so that I can
then remove all rows from that row up.

Anyone have any thoughts to help get me started on this...?

That worked perfectly to find the row. Thank you very much.

Now if I wanted to delete all the rows from 1 to that row, how would I go
about doing that?

I looked at a method for selecting them like:

Rows("1:r.address").Select

I've tried a few ways to play with the 'r' variable, but I can't seem to
pull just the row from it. So that I can select all those rows, and delete
them.


"Gary''s Student" wrote:

Sub stary()
Dim r As Range
For Each r In Selection
If IsEmpty(r.Value) Then
Else
If WorksheetFunction.Substitute(r.Value, "*", "") = "" Then
MsgBox (r.Address & " has only stars")
End If
End If
Next
End Sub

Select some cells and the macro will tell you which have only stars in them.
--
Gary's Student


"RSteph" wrote:

It's been a while since I've done much VBA with Excel, so I'm a little rusty...

I'm working on a macro to do some formatting for me. I'm pulling in a file
from another program, into Excel. I want to Search all of Column B for a
string of all *'s. I've run a few tests with pulling in the data. And there
will only ever be one cell in the column that is all *'s. The tricky part
though, is that the actual number of *'s may vary from instance to instance.

So I'm looking for a way to search the column for a cell with a variable
number of *'s, but only *'s (some cells may have *'s and text); so that I can
then remove all rows from that row up.

Anyone have any thoughts to help get me started on this...?

Actually I got it worked out now. I dropped the selection part and just used
the line:

Rows("1:" & r.Row).Delete

"RSteph" wrote:

That worked perfectly to find the row. Thank you very much.

Now if I wanted to delete all the rows from 1 to that row, how would I go
about doing that?

I looked at a method for selecting them like:

Rows("1:r.address").Select

I've tried a few ways to play with the 'r' variable, but I can't seem to
pull just the row from it. So that I can select all those rows, and delete
them.


"Gary''s Student" wrote:

Sub stary()
Dim r As Range
For Each r In Selection
If IsEmpty(r.Value) Then
Else
If WorksheetFunction.Substitute(r.Value, "*", "") = "" Then
MsgBox (r.Address & " has only stars")
End If
End If
Next
End Sub

Select some cells and the macro will tell you which have only stars in them.
--
Gary's Student


"RSteph" wrote:

It's been a while since I've done much VBA with Excel, so I'm a little rusty...

I'm working on a macro to do some formatting for me. I'm pulling in a file
from another program, into Excel. I want to Search all of Column B for a
string of all *'s. I've run a few tests with pulling in the data. And there
will only ever be one cell in the column that is all *'s. The tricky part
though, is that the actual number of *'s may vary from instance to instance.

So I'm looking for a way to search the column for a cell with a variable
number of *'s, but only *'s (some cells may have *'s and text); so that I can
then remove all rows from that row up.

Anyone have any thoughts to help get me started on this...?