Excel Misery...
-
The Question was... "I dont use visual basic but i need to select every 4th coloumn of my worksheet .. I need it for some application ... can any one tell me how to do it in visual basic." **My reply was... It is actually very easy, and I will give you the code. However, before I do I just need to know if the columns are fixed, ie you want columns D, H, L, etc? If so, something like this...
Range("F:F,H:H,J:J,L:L").Select
Should do the trick.** His comments to my answer were... I have data in columns and each column has 5 rows.. I want to select all the rows of every 4th column say A E I ... - guchu 1 hour ago and there are 2000 columns like that ... - guchu 1 hour ago I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path. I am a bit of an Excel Guru, and if any of my elves did anything like that there would be a training course approaching in the form of a large kick up the backside!
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
The Question was... "I dont use visual basic but i need to select every 4th coloumn of my worksheet .. I need it for some application ... can any one tell me how to do it in visual basic." **My reply was... It is actually very easy, and I will give you the code. However, before I do I just need to know if the columns are fixed, ie you want columns D, H, L, etc? If so, something like this...
Range("F:F,H:H,J:J,L:L").Select
Should do the trick.** His comments to my answer were... I have data in columns and each column has 5 rows.. I want to select all the rows of every 4th column say A E I ... - guchu 1 hour ago and there are 2000 columns like that ... - guchu 1 hour ago I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path. I am a bit of an Excel Guru, and if any of my elves did anything like that there would be a training course approaching in the form of a large kick up the backside!
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
Well, I know a lot of people (and I am sure all of you do), that use Excel like squared paper: They write down their numbers in Excel, but use a pocket calculator to do any math on these numbers, either because they don't know about Excel's functions or because, as one told me once, "the don't trust Excel". :doh:
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
-
In excel 2007, yes; Open workbooks Limited by available memory and system resources
**Worksheet size 1,048,576 rows by 16,384 columns**Column width 255 characters Row height 409 points Page breaks 1,026 horizontal and vertical Total number of characters that a cell can contain 32,767 characters Characters in a header or footer 255 Sheets in a workbook Limited by available memory (default is 3 sheets) Colors in a workbook 16 million colors (32 bit with full access to 24 bit color spectrum) Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a workbook Limited by available memory Unique cell formats/cell styles 64,000 Fill styles 256 Line weight and styles 256 Unique font types 1,024 global fonts available for use; 512 per workbook Number formats in a workbook Between 200 and 250, depending on the language version of Excel that you have installed Names in a workbook Limited by available memory Windows in a workbook Limited by available memory Panes in a window 4 Linked sheets Limited by available memory Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) Limited by available memory; a summary report shows only the first 251 scenarios Changing cells in a scenario 32 Adjustable cells in Solver 200 Custom functions Limited by available memory Zoom range 10 percent to 400 percent Reports Limited by available memory Sort references 64 in a single sort; unlimited when using sequential sorts Undo levels 100 Fields in a data form 32 Workbook parameters 255 parameters per workbook Filter drop-down lists 10,000Dave Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com -
ricmil42 wrote:
but is it even possible to have that many columns?
yes, I have known a few people to use Excel instead of programming, the result is data storage in columns and rows by the thousands.
_________________________ John Andrew Holmes "It is well to remember that the entire universe, with one trifling exception, is composed of others." Shhhhh.... I am not really here. I am a figment of your imagination.... I am still in my cave so this must be an illusion....
-
Well, I know a lot of people (and I am sure all of you do), that use Excel like squared paper: They write down their numbers in Excel, but use a pocket calculator to do any math on these numbers, either because they don't know about Excel's functions or because, as one told me once, "the don't trust Excel". :doh:
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
Smithers-Jones wrote:
"the don't trust Excel".
I have heard that.... My reply was "spot check. Trust, but verify."
_________________________ John Andrew Holmes "It is well to remember that the entire universe, with one trifling exception, is composed of others." Shhhhh.... I am not really here. I am a figment of your imagination.... I am still in my cave so this must be an illusion....
-
The Question was... "I dont use visual basic but i need to select every 4th coloumn of my worksheet .. I need it for some application ... can any one tell me how to do it in visual basic." **My reply was... It is actually very easy, and I will give you the code. However, before I do I just need to know if the columns are fixed, ie you want columns D, H, L, etc? If so, something like this...
Range("F:F,H:H,J:J,L:L").Select
Should do the trick.** His comments to my answer were... I have data in columns and each column has 5 rows.. I want to select all the rows of every 4th column say A E I ... - guchu 1 hour ago and there are 2000 columns like that ... - guchu 1 hour ago I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path. I am a bit of an Excel Guru, and if any of my elves did anything like that there would be a training course approaching in the form of a large kick up the backside!
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
Dalek Dave wrote:
I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path.
He may, of course, be processing data from an external source, and have no control over the original format.
-
The Question was... "I dont use visual basic but i need to select every 4th coloumn of my worksheet .. I need it for some application ... can any one tell me how to do it in visual basic." **My reply was... It is actually very easy, and I will give you the code. However, before I do I just need to know if the columns are fixed, ie you want columns D, H, L, etc? If so, something like this...
Range("F:F,H:H,J:J,L:L").Select
Should do the trick.** His comments to my answer were... I have data in columns and each column has 5 rows.. I want to select all the rows of every 4th column say A E I ... - guchu 1 hour ago and there are 2000 columns like that ... - guchu 1 hour ago I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path. I am a bit of an Excel Guru, and if any of my elves did anything like that there would be a training course approaching in the form of a large kick up the backside!
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
Dalek Dave wrote:
I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path.
He may, of course, be processing data from an external source, and have no control over the original format.
Simple Macro would switch it around.
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
Dalek Dave wrote:
I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path.
Why?
Agh! Reality! My Archnemesis![^]
| FoldWithUs! | sighist | µLaunch - program launcher for server core and hyper-v server.Ease of use. To skim through the data one need only use the mouse wheel rather than the sliders at the bottom. Sorting. Easier to perform a sort verically than horizontally. Printing. Try printing 2000 columns and your paper costs would be through the roof. Insert/Delete information. Only have to identify the column and whizz down, rather than reading across, visually harder to do that.
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
Ease of use. To skim through the data one need only use the mouse wheel rather than the sliders at the bottom. Sorting. Easier to perform a sort verically than horizontally. Printing. Try printing 2000 columns and your paper costs would be through the roof. Insert/Delete information. Only have to identify the column and whizz down, rather than reading across, visually harder to do that.
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
In excel 2007, yes; Open workbooks Limited by available memory and system resources
**Worksheet size 1,048,576 rows by 16,384 columns**Column width 255 characters Row height 409 points Page breaks 1,026 horizontal and vertical Total number of characters that a cell can contain 32,767 characters Characters in a header or footer 255 Sheets in a workbook Limited by available memory (default is 3 sheets) Colors in a workbook 16 million colors (32 bit with full access to 24 bit color spectrum) Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a workbook Limited by available memory Unique cell formats/cell styles 64,000 Fill styles 256 Line weight and styles 256 Unique font types 1,024 global fonts available for use; 512 per workbook Number formats in a workbook Between 200 and 250, depending on the language version of Excel that you have installed Names in a workbook Limited by available memory Windows in a workbook Limited by available memory Panes in a window 4 Linked sheets Limited by available memory Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) Limited by available memory; a summary report shows only the first 251 scenarios Changing cells in a scenario 32 Adjustable cells in Solver 200 Custom functions Limited by available memory Zoom range 10 percent to 400 percent Reports Limited by available memory Sort references 64 in a single sort; unlimited when using sequential sorts Undo levels 100 Fields in a data form 32 Workbook parameters 255 parameters per workbook Filter drop-down lists 10,000Dave Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com -
Simple Macro would switch it around.
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
Simple Macro would switch it around.
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
Why did that deserve a one? It is a perfectly elegant solution to the problem.
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
Agreed, but no reasons to kick him out.
Agh! Reality! My Archnemesis![^]
| FoldWithUs! | sighist | µLaunch - program launcher for server core and hyper-v server.Maybe, but some Big Stick Education would help.
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
In excel 2007, yes; Open workbooks Limited by available memory and system resources
**Worksheet size 1,048,576 rows by 16,384 columns**Column width 255 characters Row height 409 points Page breaks 1,026 horizontal and vertical Total number of characters that a cell can contain 32,767 characters Characters in a header or footer 255 Sheets in a workbook Limited by available memory (default is 3 sheets) Colors in a workbook 16 million colors (32 bit with full access to 24 bit color spectrum) Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a workbook Limited by available memory Unique cell formats/cell styles 64,000 Fill styles 256 Line weight and styles 256 Unique font types 1,024 global fonts available for use; 512 per workbook Number formats in a workbook Between 200 and 250, depending on the language version of Excel that you have installed Names in a workbook Limited by available memory Windows in a workbook Limited by available memory Panes in a window 4 Linked sheets Limited by available memory Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) Limited by available memory; a summary report shows only the first 251 scenarios Changing cells in a scenario 32 Adjustable cells in Solver 200 Custom functions Limited by available memory Zoom range 10 percent to 400 percent Reports Limited by available memory Sort references 64 in a single sort; unlimited when using sequential sorts Undo levels 100 Fields in a data form 32 Workbook parameters 255 parameters per workbook Filter drop-down lists 10,000Dave Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.comi'll counter balance those uni votes too It was a good answer
Marc Clifton wrote:
That has nothing to do with VB. - Oh crap. I just defended VB!
-
The Question was... "I dont use visual basic but i need to select every 4th coloumn of my worksheet .. I need it for some application ... can any one tell me how to do it in visual basic." **My reply was... It is actually very easy, and I will give you the code. However, before I do I just need to know if the columns are fixed, ie you want columns D, H, L, etc? If so, something like this...
Range("F:F,H:H,J:J,L:L").Select
Should do the trick.** His comments to my answer were... I have data in columns and each column has 5 rows.. I want to select all the rows of every 4th column say A E I ... - guchu 1 hour ago and there are 2000 columns like that ... - guchu 1 hour ago I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path. I am a bit of an Excel Guru, and if any of my elves did anything like that there would be a training course approaching in the form of a large kick up the backside!
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
Dalek Dave wrote:
5 rows and 2000 columns
I don't use excel that often but I do sort out basic database design and that's a fricking scary table. That being said and with me being generous, he could be in the same situation I had a while ago where an extract had a small subset of information we needed from a giant table so in that sort of case, I can understand getting something like that. God I hate that extract. I'd change it if I could but it's an external source.
-
Dalek Dave wrote:
5 rows and 2000 columns
I don't use excel that often but I do sort out basic database design and that's a fricking scary table. That being said and with me being generous, he could be in the same situation I had a while ago where an extract had a small subset of information we needed from a giant table so in that sort of case, I can understand getting something like that. God I hate that extract. I'd change it if I could but it's an external source.
Oh I understand that if it is just one huge mother of a spreadsheet it would be maybe 2000 columns, but when it is only five rows you have to wonder.
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
Dalek Dave wrote:
I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path.
He may, of course, be processing data from an external source, and have no control over the original format.
Or he could be producing data for an external customer and have no control over what they use. I've got an app that drops >10M value databombs in CSV files. Excel is obviously a poor tool to try and analyze the data with; but it lets me sanity check a few values to make sure what I'm outputting makes sense. CSV is also much more flexible than writing the data in raw binary form or my having to worry about how to import it into End Users Data Bomb Viewer's native format or what happens if they want to use multiple tools that can't read each others files...
3x12=36 2x12=24 1x12=12 0x12=18
-
Smithers-Jones wrote:
"the don't trust Excel".
I have heard that.... My reply was "spot check. Trust, but verify."
_________________________ John Andrew Holmes "It is well to remember that the entire universe, with one trifling exception, is composed of others." Shhhhh.... I am not really here. I am a figment of your imagination.... I am still in my cave so this must be an illusion....
Some of my users use printing calculators exclusively - because "it is easier to spot errors that way". They calculate everything twice and if the results are not equal they just put the sheets side by side.
The bearing of a child takes nine months, no matter how many women are assigned.
-
The Question was... "I dont use visual basic but i need to select every 4th coloumn of my worksheet .. I need it for some application ... can any one tell me how to do it in visual basic." **My reply was... It is actually very easy, and I will give you the code. However, before I do I just need to know if the columns are fixed, ie you want columns D, H, L, etc? If so, something like this...
Range("F:F,H:H,J:J,L:L").Select
Should do the trick.** His comments to my answer were... I have data in columns and each column has 5 rows.. I want to select all the rows of every 4th column say A E I ... - guchu 1 hour ago and there are 2000 columns like that ... - guchu 1 hour ago I would suggest that if he has 5 rows and 2000 columns he may want to think about a different career path. I am a bit of an Excel Guru, and if any of my elves did anything like that there would be a training course approaching in the form of a large kick up the backside!
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
That's not so bad. On one project I asked if they had digital data or whether we'd have to enter it from paper and was told it's all digital. I figured that should be much easier than having to get info off of paper. Once we started the work, they gave me the data: over 2000 individual Excel spreadsheets.:confused: :wtf: X| I think paper would have been better. Since the sheets had small variations in formatting, it took me 3 days to write macros to open the sheets, extract the info and put it into 1 large sheet that I could then manipulate. After that I learned to ask better questions!
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software