delete columns based on value in one cell

[larrydave]

Excel 2003 - I am trying to delete columns A through D based on the value of
cell 1, column IT:

If cell 1, column IT = 65536, delete columns A through D

This is what I have come up with:

Sub DeleteColumns()

If Cells("1,IT") = "65536" Then
Columns("A").delete
End If

End Sub

I get a run time error 13, type mismatch.

I have tried using column numbers instead, but am still having no luck.

(huge file, lots of mixed data I'm filtering using columns IS and IT)

Thank you so much for any help you can give.

 

[larrydave]

That is awesome! Thank you.

There is one thing that I didn't forsee, however. My formulas in Columns IS
and IT also shifted left with the rest of the data. How can I keep my
formulas in those two columns?

Thanks so much!

 

[john]

as an idea, see if this approach would work.

With Worksheets("Sheet1")

If .Cells(1, 254).Value = "123" Then
.Columns("A").EntireColumn.Delete
.Range(Cells(1, 249), Cells(1, 250)).Cut
.Paste Destination:=.Cells(1, 253)
End If

End With

 

[Mike H]

Hi,

Why don't you clearcontents instead

Sub DeleteColumns()
If Range("IT1").Value = 65536 Then
Columns("A").ClearContents
End If
End Sub

Mike

 

[larrydave]

hmm...it does move the formula back, but unfortunately when it is first
shifted left, #REF replaces the original cell reference and is then copied
with that error back to the original column. One thing I forgot to tell you;
the entire IS column has a formula while only the first cell in IT has a
formula.

formula in column IS
=IF(A1<>TODAY()-1,"",A1)
=IF(A2<>TODAY()-1,"",A2)
etc

formula in IT, cell 1
=COUNTBLANK(IS:IS) (this formula is very, very slow)

thanks for your help

 

[larrydave]

My goal is to get yesterday's and today's dates into column A so I can do
some more filtering by dates (the file starts in June 08). I don't want to
have to loop through every fourth column to find my dates as the file is just
going to continue to grow (which is why I'm using columns IS and IT). I
realize as time progresses, it may get so large as to not fit into an excel
sheet at all. At that point I may have to split it across sheets, which will
mean more weird programming.

What I would like happen is the tech people here where I work allow me to
just get rid of all the old data in the file I'm importing so my original
"sweet" program would work....but they are being stubborn and will just
continue to let it eat space in the drive.

Thanks for brainstorming with me, Mike!

 

[Mike Fogleman]

What would happen if you copied Range("E:IR") to Range("A1")? Would that not
move all your data 4 columns left, leaving your formulas intact?

Mike F

 

[larrydave]

My heartfelt thanks to all who came to my rescue. I combined ideas and here
is what I came up with...it actually works! (at least until I add more pieces
to my puzzle )

Sub ClearAndPasteColumns()

If Cells(1, 254) = "65536" Then
Columns("A").EntireColumn.Clear
Columns("E:IR").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste

End If

End Sub

It gets rid of the first four columns and moves the rest of the data to the
left, leaving my formulas intact!

Again, thank you all for your knowledge and help!