Running macro on the perticular worksheet only

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vanessa h - 25 Jan 2006 06:21 GMT

Hi,

I have an excel workbook which contains several excel worksheets. Now I
have one macro for deleting perticular rows. But I want to run the macro
only on the one of the worksheets in the workbook which contains the
word "Vanessa". i.e, if any of the worksheets contains the data
"Vanessa" then the delete rows macro should work for that perticular
worksheet only. And not for any other worksheet.

Can ayone help?

Vanessa h

Signature

vanessa h

Reply to this Message

avveerkar - 25 Jan 2006 08:06 GMT

vanessa h Wrote:

> Hi,
>
[quoted text clipped - 8 lines]
>
> Vanessa h

Any of the worksheets contains word "Vanessa" - does it mean any of the
cells in the worksheet would have "Vanessa" as part of the larger string
and you want to scan all the sheets and all cells on the sheet for
occurence of "Vanessa" and delete a particular row from only those
sheets? Or name of a worksheet is "vanessa"? In earlier case you could
use Find method to look for "Vanessa" and loop through all sheets. But
if you have a large data on the sheets this is going to be quite slow.
I don't know how you are populating data on the sheets, but if you can
flag the sheet ( you could designate a particular cell on a sheet, say
A1 and write "1" there if data entered in any of the cells has
"Vanessa" or you can scan all the sheets once and flag those sheets
with "Vanessa") then your macro could only look for the flag and act on
those sheets instead of scanning all sheets for "Vanessa" everytime.
This will be much faster. If name of your sheet is "vanessa" then you
could just use .name property to look for the sheet.

A V Veerkar

Signature

avveerkar

Reply to this Message

vanessa h - 25 Jan 2006 08:20 GMT

Thanks for your help. But here I mean to say is I want to search any o
the worksheet containing the word 'vanessa', and then run the macro o
that perticular sheet only.

I am a newbie for excel macros. So I dont know how to add flag an
all.

Can you help

Reply to this Message

avveerkar - 25 Jan 2006 10:41 GMT

I am assuming that each worksheet has max 52 columns and 100 rows ( you
can modify the code according to your need ) and you have 10 sheets in
your workbook and you want to delete row 20 on every sheet that has
"Vanessa" in any cell in those 52 columns and 100 rows. Your macro
could be

Sub vanessa()
For i = 1 To 10 ' 10 is
number of sheets
Set rng = Worksheets(i).Range("A1:AZ100") ' range we want to
search
With rng
Set c = .Find("vanessa") 'search is not
case sensitive
If c Is Nothing Then
'do nothing
Else
.Rows(20).Delete
End If
End With
Next

End Sub

The search will not distinguish between Vanessa or vanessa if you want
to match case also then change to set c=.Find("Vanessa",
MatchCase:=True)

A V Veerkar

Signature

avveerkar

Reply to this Message