delete columns based on value in one cell
 

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:D").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.

delete columns based on value in one cell
 

You have mixed up your ranges, this work

Code
-------------------
If Cells(1, 254) = "123" The
Columns("A:D").EntireColumn.Delet
End I
-------------------

--
Simon Lloy

Regards
Simon Lloy
'The Code Cage' (http://www.thecodecage.com
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=3820

delete columns based on value in one cell
 

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!

"Simon Lloyd" wrote:


You have mixed up your ranges, this works

Code:
--------------------
If Cells(1, 254) = "123" Then
Columns("A:D").EntireColumn.Delete
End If
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38201

delete columns based on value in one cell
 

as an idea, see if this approach would work.

With Worksheets("Sheet1")

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

End With
--
jb


"larrydave" wrote:

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!

"Simon Lloyd" wrote:


You have mixed up your ranges, this works

Code:
--------------------
If Cells(1, 254) = "123" Then
Columns("A:D").EntireColumn.Delete
End If
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38201

delete columns based on value in one cell
 

Hi,

Why don't you clearcontents instead

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

Mike

"larrydave" wrote:

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:D").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.

delete columns based on value in one cell
 

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

"john" wrote:

as an idea, see if this approach would work.

With Worksheets("Sheet1")

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

End With
--
jb


"larrydave" wrote:

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!

"Simon Lloyd" wrote:


You have mixed up your ranges, this works

Code:
--------------------
If Cells(1, 254) = "123" Then
Columns("A:D").EntireColumn.Delete
End If
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38201

delete columns based on value in one cell
 

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 H" wrote:

Hi,

Why don't you clearcontents instead

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

Mike

"larrydave" wrote:

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:D").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.

delete columns based on value in one cell
 

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" wrote in message
...
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 H" wrote:

Hi,

Why don't you clearcontents instead

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

Mike

"larrydave" wrote:

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:D").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.

delete columns based on value in one cell
 

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:D").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!



"Mike Fogleman" wrote:

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" wrote in message
...
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 H" wrote:

Hi,

Why don't you clearcontents instead

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

Mike

"larrydave" wrote:

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:D").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.