Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting columns based upon the value that appears in column A
Earlier today Tom Ogilvy offered me the following solution
for deleting all columns in my spreasheet other than those that had "first name","surname" or "score" in row A. Dim rng as Range, sStr as String, i as Long set rng = cells(1,"IV").End(xltoLeft) for i = rng.column to 1 step -1 sStr = lcase(cells(1,i).Value) if sStr < "first name" and _ sStr < "surname" and _ sStr < "score" then cells(1,i).EntireColumn.Delete end if Next The solution worked as I required it to, however, I have run into problems when trying to adapt the same code on a spreadsheet that has 84 columns of which I want to delete all of them bar 7. When I re-run the macro (having change the relevant strings) all columns are deleted. Any ideas as to what may be wrong ? Again, row 1 is where all the relevant information is stored. Many thanks Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting columns based upon the value that appears in column A
Guess we would have to see what modifications you made.
-- Regards, Tom Ogilvy "Steve" wrote in message ... Earlier today Tom Ogilvy offered me the following solution for deleting all columns in my spreasheet other than those that had "first name","surname" or "score" in row A. Dim rng as Range, sStr as String, i as Long set rng = cells(1,"IV").End(xltoLeft) for i = rng.column to 1 step -1 sStr = lcase(cells(1,i).Value) if sStr < "first name" and _ sStr < "surname" and _ sStr < "score" then cells(1,i).EntireColumn.Delete end if Next The solution worked as I required it to, however, I have run into problems when trying to adapt the same code on a spreadsheet that has 84 columns of which I want to delete all of them bar 7. When I re-run the macro (having change the relevant strings) all columns are deleted. Any ideas as to what may be wrong ? Again, row 1 is where all the relevant information is stored. Many thanks Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting columns based upon the value that appears in column A
I have now found the cause of this problem. I was not
using lower case letters when stating the field names that I wanted to keep. Having deleted all columns that are no longer required, is it possible to re-order them based upon the values that appear in row A ? i.e. "Surname" appears as the first column, "First Name" the second and "Score" the third, regardless of what order they appeared in the original spreadsheet -----Original Message----- Earlier today Tom Ogilvy offered me the following solution for deleting all columns in my spreasheet other than those that had "first name","surname" or "score" in row A. Dim rng as Range, sStr as String, i as Long set rng = cells(1,"IV").End(xltoLeft) for i = rng.column to 1 step -1 sStr = lcase(cells(1,i).Value) if sStr < "first name" and _ sStr < "surname" and _ sStr < "score" then cells(1,i).EntireColumn.Delete end if Next The solution worked as I required it to, however, I have run into problems when trying to adapt the same code on a spreadsheet that has 84 columns of which I want to delete all of them bar 7. When I re-run the macro (having change the relevant strings) all columns are deleted. Any ideas as to what may be wrong ? Again, row 1 is where all the relevant information is stored. Many thanks Steve . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting columns based upon the value that appears in column A
I already gave you a solution to that.
-- Regards, Tom Ogilvy "Steve" wrote in message ... I have now found the cause of this problem. I was not using lower case letters when stating the field names that I wanted to keep. Having deleted all columns that are no longer required, is it possible to re-order them based upon the values that appear in row A ? i.e. "Surname" appears as the first column, "First Name" the second and "Score" the third, regardless of what order they appeared in the original spreadsheet -----Original Message----- Earlier today Tom Ogilvy offered me the following solution for deleting all columns in my spreasheet other than those that had "first name","surname" or "score" in row A. Dim rng as Range, sStr as String, i as Long set rng = cells(1,"IV").End(xltoLeft) for i = rng.column to 1 step -1 sStr = lcase(cells(1,i).Value) if sStr < "first name" and _ sStr < "surname" and _ sStr < "score" then cells(1,i).EntireColumn.Delete end if Next The solution worked as I required it to, however, I have run into problems when trying to adapt the same code on a spreadsheet that has 84 columns of which I want to delete all of them bar 7. When I re-run the macro (having change the relevant strings) all columns are deleted. Any ideas as to what may be wrong ? Again, row 1 is where all the relevant information is stored. Many thanks Steve . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to columns. Only the first column appears in the wizard. | Excel Discussion (Misc queries) | |||
Count the times a value appears in a column based on another value | Excel Worksheet Functions | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) | |||
Deleting columns based upon the value that appears in ROW A | Excel Programming | |||
Deleting a column based on a value | Excel Programming |