+ Reply to Thread
Results 1 to 4 of 4

Running macro on the perticular worksheet only

  1. #1
    Registered User
    Join Date
    01-23-2006
    Posts
    6

    Arrow Running macro on the perticular worksheet only

    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

  2. #2
    Forum Contributor
    Join Date
    01-10-2006
    Location
    Ahmedabad, India
    MS-Off Ver
    Office 2000
    Posts
    346
    Quote Originally Posted by vanessa h
    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
    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

  3. #3
    Registered User
    Join Date
    01-23-2006
    Posts
    6
    Thanks for your help. But here I mean to say is I want to search any of the worksheet containing the word 'vanessa', and then run the macro on that perticular sheet only.

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

    Can you help?

  4. #4
    Forum Contributor
    Join Date
    01-10-2006
    Location
    Ahmedabad, India
    MS-Off Ver
    Office 2000
    Posts
    346
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1