delete columns based on cell value?

[jbaich]

I am trying to modify this code to run in a macro that applies it 3 different worksheets in a work book using a different criteria for each sheet...

don't have much of an understanding of VBA, mostly use the recorder... anyway, I must be missing some sort of structural knowledge here, any help would be much appreciated...

Starting with a master sheet, the macro copies it 3 times and names the copies RET, OFF, and IND

For each of these sheets I want to delete all of the columns that do not have the corresponding sheet name in row 1. So for example if cell F1 in the RET sheet does not contain "RET", then I want column F deleted as well as all of the other columns that have values other than "RET" in the first cell. I would like to repeat this for each of the other sheets.

The code I'm trying to work with is

Sub sbDelete_Columns_Based_On_Criteria()
Dim lColumn As Long
Dim iCntr As Long
lColumn = 201
For iCntr = lColumn To 1 Step -1
If Cells(1, iCntr) <> "RET" Then
Columns(iCntr).Delete
End If
Next
End Sub

However I run into problems when I try to reinsert this into the macro for the other sheets with the other criteria... Also, is there any way for lColumn = 201 to auto count how many columns are in the sheet rather than me entering a number, I entered 201 in this case as I figured 201 would usually be more than enough, but maybe not always...

Thanks so much for any help!

Cheers,
Joe

[TMS]

It shouldn't be too difficult to do what you want.

However, it would make it easier to develop and test the solution if you post a sample workbook with some typical data.

Regards, TMS

[jbaich]

Thanks TMS, I will try to post a sample... as an update though, I did sort of have this working temporarily by saving the code below as separate individual macros and then executed them in the main macro with...

Application.Goto Reference:= _
"PERSONAL.XLSB!sbDelete_Columns_Based_On_Criteria_RET"
Application.Run "PERSONAL.XLSB!sbDelete_Columns_Based_On_Criteria_RET"

for some unknown reason it doesn't work anymore and gives me the following error...

Run-time error '13': Type mismatch

at the following line of the code

If Cells(1, iCntr) <> "RET" Then

not sure exactly why it worked a few minutes ago and now does not...

The macro I'm trying to use here again is

Sub sbDelete_Columns_Based_On_Criteria_RET()
Dim lColumn As Long
Dim iCntr As Long
lColumn = 201
For iCntr = lColumn To 1 Step -1
If Cells(1, iCntr) <> "RET" Then
Columns(iCntr).Delete
End If
Next
End Sub

Will try to attach a sample shortly.

Thanks,
Joe

[jbaich]

Sample Sheet.xlsm so I think I've uploaded a working sample sheet with Macros saved in the worksheet... funny thing is now it seems to work just like it did the first few times before I started getting the type mismatch error... If there's a way to get it to basically do what it's doing now, without having to do the 3 separate application.run thing and to prevent anymore weird errors, that would be awesome! also would still love to figure out a way for the "application macros" to auto figure out how many columns are in the data set and set the parameter to whatever that number is on its own.

Thanks!

BTW I've just ' commented out ' the old location of the 3 macros from the code and put in code that runs them from the worksheet rather than my personal thingy

[TMS]

Maybe this:

Please Login or Register  to view this content.

Regards, TMS

[jbaich]

Hey TMS, thanks for the help!

I noticed that the delete worksheets part doesn't actually delete any sheets... which is good because in the full report/workbook, there are additional sheets that I use vlookup to populate data to Sheet3 or "Master" and I definitely do not want to lose these, would it mess anything up if took out this section of code?

It also occurred to me that there may be instances when the 3 sheets created (RET, IND, & OFF) are not the only sheets required... is it possible to have a piece of code that creates a worksheet based on the unique values in a given row? for example in the header row of the sample sheet, the current code populates each cell with RET, IND, OFF or whatever the first 3 letters of row 22 are... could it then basically create a list of these values, remove duplicates so that only unique values remain, create a new ws for each unique value instead of the predefined "vArray = Array("IND", "OFF", "RET")" and then continue on as it currently does with the rest of the macro? so something like vArray = Array(row1 unique values only, no duplicates, repeat for each) except in VBA lingo?

If this is possible, would it also be possible to allow the user to select if they want the macro to do it's thing based on the current criteria (lets call it Model Type; RET, IND, OFF, etc) or could the user choose have the macro run on a more specific variable such as the unique values in row 22? so basically do the same thing except instead of making and sorting based on the more general RET, IND, OFF, etc, create sheets and sort specifically by "Model Name" so worksheets for IND7054, IND7107, OFF7554, etc?

I'm thinking a dialogue box prompting the user to choose between Model Type or Model Name could pop up before the macro runs and then the user could select their choice (check box?), which would then tell the macro which way to "sort" or I guess which array to use? (either row 1 or row 22?)

Again, thanks so much for your help already!

Cheers,
Joe