Sorry, I forgot to change
Dim mth() As Long
to
Dim mth() As String


"Neutron1871" wrote:

Correct, I want to delete the entire column(s). The dates in row 1 are text.
I copied and pasted values of a 'mmm-yy from a different worksheet to get
these text values in row 1.

I used your code below and it crashes stating "Type Mismatch":
mth(i) = InputBox("Enter " & Application.Choose(i, "First", _
"Second", "Third", "Fourth") & " Reporting Month (mmm-yy)", _
"Rpt Mth " & i)

Here is some data from the table in case you need it:
Name Dec-05 Jan-06 Feb-06 Mar-06 0
Hetfield 8.31797508 13.21934081 17.25015038 3.12754056
Akerfeldt 4.84123504 4.93747796 5.51696188 3.91421368






"JMB" wrote:

Do you want to delete the entire column(s)? Be sure to back up your data
before trying. Also, are the dates in Row1 dates formatted as "mmm-yy" or
are they text? Inputbox returns text - so if the data in row one are numbers
(date values) it won't match.

Sub test()
Dim i As Long
Dim mthcount As Long
Dim mth() As Long
Dim rngCell As Range
Dim rngDelete As Range

mthcount = CLng(InputBox("Enter # of Months to Report", "Rpt Mths #"))
ReDim mth(1 To mthcount)

For i = 1 To mthcount
mth(i) = InputBox("Enter " & Application.Choose(i, "First", _
"Second", "Third", "Fourth") & " Reporting Month (mmm-yy)", _
"Rpt Mth " & i)
Next i

For Each rngCell In Rows("1").Cells
If IsError(Application.Match(rngCell.Value, mth, 0)) And _
rngCell.Value < "Name" And rngCell.Value < "" Then
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else: Set rngDelete = Union(rngDelete, rngCell)
End If
End If
Next rngCell

If Not rngDelete Is Nothing Then _
rngDelete.EntireColumn.Delete

End Sub


"Neutron1871" wrote:

I am trying to figure out how to make the below VB run correctly. This code
selects row 1 and then based on what is in each cell, deletes any column that
does not have 1 of 4 variables, is not blank, and does not contain the word
"Name". Please help!!


' If statement to ask user what months are being reported on and formats
accordingly.
mthcount = InputBox("Enter # of Months to Report", "Rpt Mths #")

If mthcount = 2 Then
mthone = InputBox("Enter First Reporting Month (mmm-yy)", "Rpt Mth 1")
mthtwo = InputBox("Enter Second Reporting Month (mmm-yy)", "Rpt Mth
2")
ElseIf mthcount = 3 Then
mthone = InputBox("Enter First Reporting Month (mmm-yy)", "Rpt Mth 1")
mthtwo = InputBox("Enter Second Reporting Month (mmm-yy)", "Rpt Mth
2")
mththree = InputBox("Enter Third Reporting Month (mmm-yy)", "Rpt Mth
3")
ElseIf mthcount = 4 Then
mthone = InputBox("Enter First Reporting Month (mmm-yy)", "Rpt Mth 1")
mthtwo = InputBox("Enter Second Reporting Month (mmm-yy)", "Rpt Mth
2")
mththree = InputBox("Enter Third Reporting Month (mmm-yy)", "Rpt Mth
3")
mthfour = InputBox("Enter Fourth Reporting Month (mmm-yy)", "Rpt Mth
4")
End If

Rows("1").Select
If Rows("1").Select < mthone Or mthtwo Or mththree Or mthfour Or "" Or
"Name" Then
Column.Delete
End If