Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row off 5 worksheets containing specific value
Good Afternoon all,
Wonder if someone could give me a suggestion here - I have multiple worksheets in a workbook. What I would like to do is have the user type in a value into an input box, and if that value is found either in column A or column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on all 5) for it to delete the entire row. Can this be done? if so, please could you suggest a method Thanks so much, I appreciate the assistance. Regards, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row off 5 worksheets containing specific value
If it is on one sheet, will it be on the same row on the other four sheets
or just somewhere else on those other sheets? Can the value be on a single sheet in more than one row or, once found on a sheet, will that be the only occurrence of that value on the sheet? -- Rick (MVP - Excel) "PVANS" wrote in message ... Good Afternoon all, Wonder if someone could give me a suggestion here - I have multiple worksheets in a workbook. What I would like to do is have the user type in a value into an input box, and if that value is found either in column A or column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on all 5) for it to delete the entire row. Can this be done? if so, please could you suggest a method Thanks so much, I appreciate the assistance. Regards, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row off 5 worksheets containing specific value
Try this:
Sub deleteRow() Dim lr As Long, sh As Worksheet, rng As Range matchThis = InputBox("Enter data to match for row deletion.", "DATA TO MATCH") For Each sh In ThisWorkbook.Sheets lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _ LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, MatchCase:=False).Row Set rng = sh.Range("A2:B" & lr) For Each c In rng If c.Value = matchThis Then sh.Rows(c.Row).Delete End If Next Next End Sub "PVANS" wrote in message ... Good Afternoon all, Wonder if someone could give me a suggestion here - I have multiple worksheets in a workbook. What I would like to do is have the user type in a value into an input box, and if that value is found either in column A or column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on all 5) for it to delete the entire row. Can this be done? if so, please could you suggest a method Thanks so much, I appreciate the assistance. Regards, Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row off 5 worksheets containing specific value
Will it be on the same row in all sheets?
Could there be multiple instances of the value been sought? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Good Afternoon all, Wonder if someone could give me a suggestion here - I have multiple worksheets in a workbook. What I would like to do is have the user type in a value into an input box, and if that value is found either in column A or column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on all 5) for it to delete the entire row. Can this be done? if so, please could you suggest a method Thanks so much, I appreciate the assistance. Regards, Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row off 5 worksheets containing specific value
Hi Rick,
It will only appear once on each sheet - but not definitely on the same numbered row definitely in either column A or B and definetly on every sheet. If it would be easier, I am able to change the one sheet slightly so that the value will definitely be in column B on all 5 sheets "Rick Rothstein" wrote: If it is on one sheet, will it be on the same row on the other four sheets or just somewhere else on those other sheets? Can the value be on a single sheet in more than one row or, once found on a sheet, will that be the only occurrence of that value on the sheet? -- Rick (MVP - Excel) "PVANS" wrote in message ... Good Afternoon all, Wonder if someone could give me a suggestion here - I have multiple worksheets in a workbook. What I would like to do is have the user type in a value into an input box, and if that value is found either in column A or column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on all 5) for it to delete the entire row. Can this be done? if so, please could you suggest a method Thanks so much, I appreciate the assistance. Regards, Paul . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row off 5 worksheets containing specific value
Hi Mike,
There will only be one instance of this value However, it won't necessariliy be on the same row on all sheets. I can make it so it is definitely in column B not Column A or B if that makes it easier "Mike H" wrote: Will it be on the same row in all sheets? Could there be multiple instances of the value been sought? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Good Afternoon all, Wonder if someone could give me a suggestion here - I have multiple worksheets in a workbook. What I would like to do is have the user type in a value into an input box, and if that value is found either in column A or column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on all 5) for it to delete the entire row. Can this be done? if so, please could you suggest a method Thanks so much, I appreciate the assistance. Regards, Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row off 5 worksheets containing specific value
I can make it so it is definitely in column B not Column A or B if that
makes it easier No that's fine working on it now -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Hi Mike, There will only be one instance of this value However, it won't necessariliy be on the same row on all sheets. I can make it so it is definitely in column B not Column A or B if that makes it easier "Mike H" wrote: Will it be on the same row in all sheets? Could there be multiple instances of the value been sought? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Good Afternoon all, Wonder if someone could give me a suggestion here - I have multiple worksheets in a workbook. What I would like to do is have the user type in a value into an input box, and if that value is found either in column A or column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on all 5) for it to delete the entire row. Can this be done? if so, please could you suggest a method Thanks so much, I appreciate the assistance. Regards, Paul |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row off 5 worksheets containing specific value
Hi,
Try this. I'm not sure I've used the best compare method. I have assumed the value being looked for is the only value in the cell and not part of a larger string Sub ClearIt() Dim Ws As Worksheet Dim ResPonse As String Dim LastRow As Long Dim LastrowA As Long Set sht = Sheets("Sheet1") S = "Sheet1,Sheet2,Sheet3,Sheet4,Sheet5" V = Split(S, ",") ResPonse = UCase(InputBox("Enter value")) If ResPonse = vbNullString Then Exit Sub LastRow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row For x = LastRow To 1 Step -1 If UCase(Cells(x, 1)) = ResPonse Or UCase(Cells(x, 2)) = ResPonse Then For Each Ws In ThisWorkbook.Worksheets If Not IsError(Application.Match(Ws.Name, V, 0)) Then LastrowA = WorksheetFunction.Max(Ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row, Ws.Cells(Cells.Rows.Count, "B").End(xlUp).Row) Set MyRange = Ws.Range("A1:A" & LastrowA) For Each c In MyRange If UCase(c.Value) = ResPonse Or UCase(c.Offset(, 1).Value) = ResPonse Then c.EntireRow.Delete Exit For End If Next End If Next Ws End If Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: I can make it so it is definitely in column B not Column A or B if that makes it easier No that's fine working on it now -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Hi Mike, There will only be one instance of this value However, it won't necessariliy be on the same row on all sheets. I can make it so it is definitely in column B not Column A or B if that makes it easier "Mike H" wrote: Will it be on the same row in all sheets? Could there be multiple instances of the value been sought? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Good Afternoon all, Wonder if someone could give me a suggestion here - I have multiple worksheets in a workbook. What I would like to do is have the user type in a value into an input box, and if that value is found either in column A or column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on all 5) for it to delete the entire row. Can this be done? if so, please could you suggest a method Thanks so much, I appreciate the assistance. Regards, Paul |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row off 5 worksheets containing specific value
Being in either Column A or B is not a problem. Give this macro a try...
Sub ClearResponseRows() Dim X As Long, WS As Worksheet, Response As String, S() As String Response = UCase(InputBox("Enter value")) If Response = vbNullString Then Exit Sub S = Split("Sheet1,Sheet2,Sheet3,Sheet4,Sheet5", ",") On Error GoTo NotFound For X = 0 To UBound(S) Worksheets(S(X)).Range("A:B").Find(What:=Response, LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=False).EntireRow.Delete Next NotFound: End Sub Note: If you need to change the sheet names this macro applies to, you would change the obvious text String argument to the Split function (4th statement in the macro)... the text is comma delimited with no spaces around the commas. -- Rick (MVP - Excel) "PVANS" wrote in message ... Hi Mike, There will only be one instance of this value However, it won't necessariliy be on the same row on all sheets. I can make it so it is definitely in column B not Column A or B if that makes it easier "Mike H" wrote: Will it be on the same row in all sheets? Could there be multiple instances of the value been sought? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Good Afternoon all, Wonder if someone could give me a suggestion here - I have multiple worksheets in a workbook. What I would like to do is have the user type in a value into an input box, and if that value is found either in column A or column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on all 5) for it to delete the entire row. Can this be done? if so, please could you suggest a method Thanks so much, I appreciate the assistance. Regards, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba to delete specific name from Name Manager | Excel Programming | |||
Delete Specific Sheets | Excel Programming | |||
Macro to delete specific rows in different worksheets | Excel Programming | |||
print specific worksheets in specific order. | Excel Programming |