Seach for string in all colums and sheet and delete row(s) is perfect match

[piddy]

Hi

I hope you can help me. The following macro works but it also deletes columms that partly contains the string its searching for.

'Sub Remove_columns()
'    Dim wS As Worksheet
'    For Each wS In ThisWorkbook.Worksheets
'        With wS
'            For i = .Columns.Count To 1 Step -1
'                If InStr(1, LCase(.Cells(1, i)), LCase("Cprnr")) Then .Columns(i).EntireColumn.Delete
'                If InStr(1, LCase(.Cells(1, i)), LCase("Cvrnr")) Then .Columns(i).EntireColumn.Delete
'                If InStr(1, LCase(.Cells(1, i)), LCase("Navn")) Then .Columns(i).EntireColumn.Delete
'            Next i
'        End With 'wS
'    Next wS
'End Sub

So if a columm is called procesnavn it also deletes the columm. How can I alter the macro so it only deletes a columms if it's a 100 % match?

Thanks in advanced.

Kind regards
Thomas

 

[Rick Rothstein]

Hi Rick

I got the following working now:

In a module:

[B][COLOR="#FF0000"]Global Has_GDPR_Run As Boolean[/COLOR][/B]

Sub Remove_GDPR()
Dim a As Long, w As Long, vdelcols As Variant, vcolndx As Variant
vdelcols = Array("Cprnr", "Cvrnr", "Navn")
[B][COLOR="#FF0000"]Has_GDPR_Run = True[/COLOR][/B]
With ThisWorkbook
    For w = 1 To .Worksheets.Count
        With Worksheets(w)
            For a = LBound(vdelcols) To UBound(vdelcols)
                vcolndx = Application.Match(vdelcols(a), .Rows(1), 0)
                If Not IsError(vcolndx) Then
                    .Columns(vcolndx).EntireColumn.Delete
                End If
            Next a
        End With
    Next w
End With
End Sub

If File is saved the following is run in Thisworkbook:

Option Explicit
 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     Static bolInProcess As Boolean

     Call Remove_GDPR

     [B][COLOR="#FF0000"]If Has_GDPR_Run Then [/COLOR][/B]MsgBox "File is not saved without personal information"
 End Sub

The Msgbox now comes up every time I click save. Is there a way to code it so it only comes up when the "Remove_GDPR" macro has been run?

See if making the changes highlighted in red above makes the code do what you want. Note that the "Global" line of code exists on a line by itself outside of any procedures.

 

[piddy]

I get the following error:

"Compile error: Only comments may appear after end sub, end function or end property"

I think its caused by the

Global Has_GDPR_run as Boolean

Should that line be a specific place so it doesn't cause problems?

 

[Rick Rothstein]

I get the following error:

"Compile error: Only comments may appear after end sub, end function or end property"

I think its caused by the

Global Has_GDPR_run as Boolean

Should that line be a specific place so it doesn't cause problems?

It should be at the top of the module, above any and all of your procedures (functions, subs).