Delete row sheet 'x' if cell in sheet 'X' contains name of sheet 'y'

Question

second Q here, still learning but trying to do my best!

Question:

I want to run a macro which takes the name of a sheet (it is the active sheet at that moment) and uses it to delete every row in sheet "PD" which contains the name of that "previous active sheet" in column "M". Than the macro should go back to that "previous active sheet" and fill some cells with color (that part should be ok)

I tried a couple of things and with help of other topics below and the record button i managed to get this code, which doesn't work

Sub FindandDelete
Sheets("PD").Select
Range("M").Select
With ActiveSheet
.AutoFilterMode = False
With Range("M1", Range("M" & Rows.Count).End(xlUp))
    .AutoFilter 1, ActiveSheet.Previous.Name.Select
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
ActiveSheet.Previous.Select
Range("N16,N17").Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 49407
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Range("A1").Select
End Sub

For activate sheet: How to activate a specific worksheet in Excel

Delete row: http://www.mrexcel.com/forum/excel-questions/537771-delete-row-if-specific-cell-value-matches-value-found-another-worksheet.html

Hope that anyone can help, if more effort or explanation needed, happy to hear.

`takes the name of an random sheet (it is the active sheet at that moment)` if you want to take any random sheet then why did you mention `(it is the active sheet at that moment)` ? — Stupid_Intern, Apr 25 '16 at 14:24
I **highly** suggest reading through [this SO thread](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) on how to avoid using `.Select`\`.Activate`, it can save you many headaches. — BruceWayne, Apr 25 '16 at 14:30
@newguy: I edited my question: it should 'take' the name of the sheet which is the active sheet at that moment, but that active sheet can differ (depending on which sheet is selected), so indeed, not a random sheet. hopefully clear now. — bart1701, Apr 25 '16 at 14:32
Are you cycling through all the sheets or just checking one sheet? — findwindow, Apr 25 '16 at 14:51
@findwindow: I am at one specific sheet, decide that i want to delete the data from that sheet in the PD sheet, so then i can run the macro which will do it for me.. is that clear? — bart1701, Apr 25 '16 at 15:03
Yea, one more question (I hope), is the name of that sheet the only string in column M or does the cell contain other words? — findwindow, Apr 25 '16 at 15:12
@findwindow No other words: to give a bit more info: sheets are named from 501, 502 onwards.. and sheet M only contains 501, 502 or ect. — bart1701, Apr 25 '16 at 15:16

Sergej · Accepted Answer · 2016-04-25T14:59:03.550

2

To be honest your code might work with some adjustments, but I'd rather start from scratch and use this:

Sub FindAndDelete
    Dim strAName As String
    Dim lngCounter as Long
    strAName = ActiveSheet.Name

    With Worksheets("PD")
        For lngCounter = .Cells(Rows.Count, 13).End(xlUp).row to 1  Step -1
            if .Cells(lngcounter, 13).value = strAName then
                .Rows(lngCounter).Delete
            end if
        Next lngCounter
    End with

    'Do your coloring stuff, which you said is fine now
End Sub

You should avoid changing sheets. There usually is no need to "activate" or "select" anyhting in VBA, it is just something humans have to do and therefore the MacroRecorder uses it...

edited Apr 25 '16 at 14:59

answered Apr 25 '16 at 14:53

Sergej

376
4
15

1

It will leave some rows as deleting the row shifts it up hence you have to decement the lncounter by 1 every time you delete a row or it's better to loop from last row in the usedrange to the very first – Stupid_Intern Apr 25 '16 at 14:58
@sergej: thanks for your effort. Not sure why but if I run in the Active Sheet, than unfortunately, nothing happens? any clue why? – bart1701 Apr 25 '16 at 15:04
@bart1701 Hard to say without your workbook to test it. If there is no error message I'd say that the code works, but the logic does not qualify your requirements – Sergej Apr 25 '16 at 15:07
@bart1701 Use `Debug.Print ActiveSheet.name` at the start of your code to see which is the activesheet similarly you can check other variables value at any point in the code. You will get the value in the Immediate windows access it using Ctrl + G in the Editor – Stupid_Intern Apr 25 '16 at 15:07

Delete row sheet 'x' if cell in sheet 'X' contains name of sheet 'y'

1 Answers1