Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete columns based on cell value | Excel Programming | |||
Delete Row Based On Two Columns | Excel Programming | |||
How can I delete a row based on Columns H and I | Excel Programming | |||
How can I delete a row based on Columns H and I | Excel Programming | |||
Delete Columns based on a condition | Excel Programming |