Excel Misery...
-
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
-
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
-
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
before reading your message, this elv had no idea how to swap row and colums. however, little goggling revealed the method. 1. select your range and click copy. 2. then click on paste special. a special dialog will open. check transpose and click ok. what macro would you use for same? I rarely use excel and my knowledge is limited to few formulas in it. but I know there's lot of programming capabilities in it.
-
before reading your message, this elv had no idea how to swap row and colums. however, little goggling revealed the method. 1. select your range and click copy. 2. then click on paste special. a special dialog will open. check transpose and click ok. what macro would you use for same? I rarely use excel and my knowledge is limited to few formulas in it. but I know there's lot of programming capabilities in it.
-
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)
-
That is my wife... I have shown her twice how to generate formulas, but she would rather crunch the numbers on her printing calculator. Lucky for her, there are only 8 rows and 1 calculation she wants. She is basically using Excel to format a table.
It must be a women thing because my wife does the same thing.
-
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
Certainly - but do you want to itterate through the columns, doing something to each 4th, or select all of the 4th columns as a group and then do something?
Dim i As Integer For i = 0 To ActiveSheet.Columns.Count Step 4 ActiveSheet.Columns(1).Select ' do something to each column in succession ' ' Next i ' Or, to grab them all: Dim strRange As String strRange = "" ' Build the list of column addresses For i = 1 To 130 Step 4 ' Trying to select all of the 4th columns (from 1 to ActiveSheet.Columns.Count) ' resulted in an error - might be a limitation in Excel's string length for a list ' of range addresses. I was able to select additional columns manually. ' strRange = strRange & ActiveSheet.Columns(i).Address & "," Next i ' Trim the trailing comma from the range address list. strRange = Left(strRange, Len(strRange) - 1) Range(strRange).Select ' Do something to the columns all at once ' '-Bob
-
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
REMINDER ! MS Windows usually comes with an Excel database type driver (also one for text files). Using this, you could have had a small (real) program to get the list of files and open and read them for you. The file list could be ordered by whatever criteria you used as a people. The gathered data could (if so desired) be written to yet another spreadsheet.
/xml>
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein
"As far as we know, our computer has never had an undetected error." - Weisert
"If you are searching for perfection in others, then you seek dissappointment. If you are searching for perfection in yourself, then you seek failure." - Balboos HaGadol Mar 2010