The editor here messed up one line of code in mine - and if you copy from
that, you'll get a runtime error. Here's code you should be able to copy and
paste without error:
Sub DeleteMiddleColumn()
'any column that is between the two with the
'defined labels will be deleted.
'only works with 1 column between the two
'so with 'Column 1' | newCol | 'Column 2'
'labels, the 'newCol' column would be deleted
'regardless of how it is labeled.
Const keeperCol1 = "Column 1" ' change to actual label
Const keeperCol2 = "Column 2" ' change to actual label
Dim lastColumn As Long
Dim titleRange As Range
Dim anyTitle As Range
lastColumn = Range("A1").Offset(0, _
Columns.Count - 1).End(xlToLeft).Column
Set titleRange = Range("A1", Cells(1, lastColumn))
For Each anyTitle In titleRange
If UCase(anyTitle.Value) = UCase(keeperCol1) And _
UCase(anyTitle.Offset(0, 2).Value) = UCase(keeperCol2) Then
anyTitle.Offset(0, 1).EntireColumn.Delete
'alldone, can exit
Exit For
End If
Next
Set titleRange = Nothing
End Sub
"JLatham" wrote:
> Try this code instead? I'm not sure what was going on in the previously
> provided code, so I just wrote this from scratch. If you have any questions,
> just ask.
>
> This goes into a regular code module and you access it using Tools | Macro |
> Macros
>
> Sub DeleteMiddleColumn()
> 'any column that is between the two with the
> 'defined labels will be deleted.
> 'only works with 1 column between the two
> 'so with 'Column 1' | newCol | 'Column 2'
> 'labels, the 'newCol' column would be deleted
> 'regardless of how it is labeled.
>
> Const keeperCol1 = "Column 1" ' change to actual label
> Const keeperCol2 = "Column 2" ' change to actual label
> Dim lastColumn As Long
> Dim titleRange As Range
> Dim anyTitle As Range
>
> lastColumn = Range("A1").Offset(0, Columns.Count - 1).End(xlToLeft).Column
> Set titleRange = Range("A1", Cells(1, lastColumn))
> For Each anyTitle In titleRange
> If UCase(anyTitle.Value) = UCase(keeperCol1) And _
> UCase(anyTitle.Offset(0, 2).Value) = UCase(keeperCol2) Then
> anyTitle.Offset(0, 1).EntireColumn.Delete
> 'alldone, can exit
> Exit For
> End If
> Next
> Set titleRange = Nothing
>
> End Sub
>
>
> "David A." wrote:
>
> > Its not liking the If C <> "Ralph" Or .Name <> "Irvin" Or C.Name <> "Melvin"
> > Then
> > statment. I have change it to and and or but still not working.
> >
> > "JLGWhiz" wrote:
> >
> > > Assume that you name three columns "Ralph", "Irvin" and "Melvin" respectively.
> > >
> > > lc = Cells(1,Columns.Count).End(xlToLeft).Column
> > > myRng = Range("A1", Cells(1, lc))
> > > For Each C In myRng
> > > If C <> "Ralph" Or .Name <> "Irvin" Or C.Name <> "Melvin" Then
> > > C.EntireColumn.Delete
> > > End If
> > > Next
> > >
> > > I didn't test this so you should before you install it in your regular code.
> > >
> > >
> > > "David A." wrote:
> > >
> > > > I have a spreadsheet that people keep adding columns to. I import this sheet
> > > > and I have a macro that I hide the unwanted columns. The problem is that I
> > > > have to re-write the macro every time they add another (or change) column. I
> > > > need to write a macro that will delete the unwanted columns without
> > > > re-writing the macro.
> > > > EX:
> > > > "Column1" "Column2 "Column3"
> > > > I want to keep Column1 and Column3 and delete Column2 no matter what its
> > > > name or possition.
> >
|
Similar Threads
