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.

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

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

Hi,

Why don't you clearcontents instead

Sub DeleteColumns()
If Range("IT1").Value = 65536 Then
Columns("A").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").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.
>
>
>

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

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

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" <(E-Mail Removed)> wrote in message
news:32C3518D-5058-49DA-95E6-(E-Mail Removed)...
> 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").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").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.
>> >
>> >
>> >

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!



"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" <(E-Mail Removed)> wrote in message
> news:32C3518D-5058-49DA-95E6-(E-Mail Removed)...
> > 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").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").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.
> >> >
> >> >
> >> >
>
>
>