Find criteria in a row then delete columns past but to a stop point

[Danoz]

This is a different topic, in the previous one it is to delete rows, and this one is to delete columns. I suggest you create a new thread.

Thanks Dante Amor for your assistance with the rows query.

This is a separate query regarding deleting columns once criteria is met, then stopping at a point.



I have a variable named value "MAX" which can be between 1 & 12. 1 to 12 is numbered in 'E3' to 'P3' of the worksheet, 'A3' to 'D3' are blank. I want to delete the columns where the value is greater than MAX, but not if MAX =12, or to stop once the script hits 12 in 'P3'.

Here's what I have so far, which is based on a 2009 q&a somewhere in the forum so apologies if it's outdated.

"Delete unneeded columns in selections"

Dim LR As Long, i As Long
Application.ScreenUpdating = False
If MAX = 12 Then End If -------this is where it sticks-------
Else:LR = Range(Columns.Count & "3").End(xlLeft).Column
For i = LR To 1 Step -1
With Range(i & "3")
If .Value = 12 Then End If
Else:
If .Value > MAX Then .Resize(7).EntireColumn.Delete
End With
Next i
Application.ScreenUpdating = True


I've had a go at amending since the first attempt, but still no luck. Here's my current version that doesn't work.

' Delete unneeded columns in selections
    If MAXPYMTS >= 12 Then
        Range("B1").Select
        
        End If
        
    LR = Range(Columns.Count & "3").End(xlLeft).Column
    For i = LR To 1 Step -1
        If MAXPYMTS <= "11" And .Value > MAXPYMTS Then
        With Range(i & "3")
        .Select.EntireColumn.Delete
        End With
    Next i
    
    Else:
        If .Value = 12 Then
    End If
        
    Range("B1").Select
        
    Application.ScreenUpdating = True

 

[Fluff]

How about

Sub Danoz()
   Dim Mx As Long
   
   Mx = 3
   If Mx = 12 Then Exit Sub
   Range("P:P").Offset(, Mx - 11).Resize(, 12 - Mx).Delete
   
End Sub

 

[Danoz]

How about

Sub Danoz()
   Dim Mx As Long
   
   Mx = 3
   If Mx = 12 Then Exit Sub
   Range("P:P").Offset(, Mx - 11).Resize(, 12 - Mx).Delete
   
End Sub

Thanks for your fast reply.

Sorry, the named variable "MAX" is only variable based on static data populated by users, and is different for each use.

Does that change you suggestion?

 

[Fluff]

As long as you have a variable that holds a number between 1 & 12 inclusive, then, No.

 

[DanteAmor]

Thanks for your fast reply.

Sorry, the named variable "MAX" is only variable based on static data populated by users, and is different for each use.

Does that change you suggestion?

Did you test the macro?
How the user will populate the variable?

Try this:

Sub test()
   Dim Mx As Variant
   Mx = InputBox("Max value")
   If Mx >= 12 Then Exit Sub
   Columns(5 + Mx).Resize(, 12 - Mx).Delete
End Sub

 

[Danoz]

For example a user creates a new workbook with a new set of data, with max = 4. They don't need the columns pertaining to 5-12, so the columns can be deleted. Next user has max = 12 so all columns needed, third user has max = 2, so only requires 2 columns.

This particular worksheet, 'Selections', is populated by the macro from the static data pasted in by the user, and after the macro runs, the data is not changed. Instead a new workbook is created each time a new data set is used.

I'm trying to use the names MAX value to only keep that number of columns to be populated. The less data after the macro has run, the better, as the workbooks can get up to 10-15mb each occasionally, slowing opening/saving, making review difficult. So less guff active cells is better. To this point I've only been able to hide unneeded columns, deleting corrupts the workbook, so I'm hoping to slimline this part for multiple positive outcomes.

Any other suggestions welcome, of course.

 

[Danoz]

Did you test the macro?
How the user will populate the variable?

Try this:

Sub test()
   Dim Mx As Variant
   Mx = InputBox("Max value")
   If Mx >= 12 Then Exit Sub
   Columns(5 + Mx).Resize(, 12 - Mx).Delete
End Sub

I'm having a look at this now, thanks to you both

 

[Danoz]

Thanks Fluff and Dante, works just as you knew it would. Thanks so much for helping.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Danoz]

Did you test the macro?
How the user will populate the variable?

Try this:

Sub test()
   Dim Mx As Variant
   Mx = InputBox("Max value")
   If Mx >= 12 Then Exit Sub
   Columns(5 + Mx).Resize(, 12 - Mx).Delete
End Sub

For better or worse, I don't exit and enter subs in the script, it follows on in recognizable and named sections. Is there another way to ignore when MAX =12 rather than to exit sub?