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.

Name the keepers and write the code to delete all columns except the keepers.

"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.

Cool, thats what I am having problems with. What would that look like. I keep
try IF statements. How do I get it to look for the names without choseing a
cell,
A1.....
A2....
I wont really know what column the names will be in.

"JLGWhiz" wrote:

Name the keepers and write the code to delete all columns except the keepers.

"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.

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.

David, you might have to change the Or to And for that to work right. I get
confused on the logic of those sometimes when evaluating negative conditions.

"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.

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.