Deleting the same column nr's across multiple sheets based on dynamic cell values

[Viper147]

Good day all
Been working on this piece of code that is supposed to delete a dynamic range of columns based on values in a range of cells that will differ from time to time, across multiple sheets. The values will be the headings of each column and the column numbers will be the same across all the worksheets, which it makes a little easier. Also, the sheets will be all worksheets, not just a select few. I've now tried various options like arrays to loops and just cannot get it to delete the columns in the rest of the sheets.

Below is the piece of code that I have now, but getting stuck on the line noted below with a Type mismatch error. Thanks in advance for the assistance.

Option Explicit
Sub DeleteColumns()
    Dim VarArr As Variant
    Dim u As Range
    Dim Cel As Range
    Dim TestRng As Range
    Dim TestSht As Variant
    Dim b As Integer
    Dim ws As Worksheet
    Dim a As Long
    Dim rng As Range
    
    a = Range("M1").Value
    b = 1
    
    VarArr = Array(Range(Cells(1, 14), Cells(1, 14 + a - 1)))
    Set TestRng = Range(Cells(1, 1), Cells(1, 10))
    For Each Cel In TestRng
        If IsError(Application.Match(Cel.Value, VarArr, 0)) Then
            If Not u Is Nothing Then
              Set u = Union(u, Cel)
            Else
              Set u = Cel
            End If
        End If
    Next
    If Not u Is Nothing Then
    
    Set rng = u.Address 'Type mismatch error on this line
    For Each ws In Worksheets
        ws.Range(rng).EntireColumn.Delete
    Next ws
    
    End If
    
    End Sub

 

[My Aswer Is This]

Hard to understand what you're trying to do by reading your code.
So, are you trying to delete the same column in all the worksheets in your workbook?
If so, what column are you wanting to delete in all worksheets?

If you're wanting to delete column 3 in all worksheets in your workbook the code would look like this:

Sub Rectangle1_Click()
Dim i As Long
For i = 1 To Sheets.Count
    Sheets(i).Columns(3).Delete
Next
End Sub

 

[Peter_SSs]

Try these two changes

Dim rng As Range
Dim rng As String

Set rng = u.Address
rng = u.Address

 

[Viper147]

Sorry about the cryptic post, should've expanded on the problem.
Nonetheless, I've tried the suggestions of Peter above and all works just fine now. Thank you both for taking the time to assist, greatly appreciate it.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.