I know absolutely nothing about VBA and I haven't found much help via Google.

Here's the deal: I have a sheet with 50+ columns, 900+ rows of data. I need to reformat about 10 of those columns and stick them in a new workbook.

So, first question. How do I programmatically select every non-blank row in a column of book1, run it through some functions, and drop the results in book2?

5/4/2009 2:17:24 PM

attn agentlion

5/4/2009 2:20:11 PM

Here's the MS Excel Object Model:
http://msdn.microsoft.com/en-us/library/aa168292(office.11).aspx

Check out the Range section. I think this might be the most relevant to get you started:

Quote :

"' Visual Basic rng = DirectCast(ws.Rows(1), Excel.Range) rng = DirectCast(ws.Rows("1:3"), Excel.Range) rng = DirectCast(ws.Columns(3), Excel.Range) // C# rng = (Excel.Range)ws.Rows[1, Type.Missing]; rng = (Excel.Range)ws.Rows["1:3", Type.Missing]; rng = (Excel.Range)ws.Columns[3, Type.Missing];"

5/4/2009 3:22:15 PM

i try to use VBA as a last resort. Not because there's anything wrong with it, but because it's unnecessarily complex for so many things that Excel can do natively.

first, if this is a one-off operation, you probably don't need VBA. Are you doing this once, or many times or on a regularly basis?


There are several ways to select non-blank rows without using VBA. I would suggest to enable AutoFilter, then in the AutoFilter dropdown for one of the columns, select "(NonBlanks)". Then highlight all of the rows you want, and go to Edit > Go To > Special > Visible Cells Only. That will de-select all of the hidden cells. Then just copy and paste into a new worksheet.

It's likely that whatever reformatting you need to do can also be done with normal Excel functions also.

5/4/2009 8:30:34 PM

It's something that will likely be done on a daily basis, hundreds of times over. The basic idea (no pun intended) is that I need to write something that will streamline the extraction of info from Excel sheets. The guys that have been working with Excel have been doing it all by hand; it's taking them as long as a week to do something that could be done in an hour with the proper app.

I've never worked with VB before and every tutorial I've found is outdated or just plain terrible.

So fuck it, VB is dense and stupid. I'm already hacking something up using CSV files in PHP and MySQL.

Thanks for the support, though.

--

All is not lost! I got 10 rep points on stackoverflow for my question!

Two fleshed out answers that I have yet to test (and probably never will):

http://stackoverflow.com/questions/821364/selecting-non-blank-cells-in-excel-with-vba

[Edited on May 4, 2009 at 8:52 PM. Reason : ]

5/4/2009 8:38:10 PM

shit, just turn on the macro recorder, do what agentlion told you, then hack together the looping across sheets thing

5/4/2009 9:02:38 PM

I played around with the recorder for a minute but it felt dirty, like a cheap trick or something. I only code in binary.

5/4/2009 9:05:26 PM

if you PM me, i'll give you my email address. If you have a copy of the worksheet that you can share with me and some more details of what exactly you're trying to do, I can whip up a macro for you

5/4/2009 9:07:33 PM

To further the conversation, here's a portion of the PM I sent agentlion:

The more I think about it, the more I'd ultimately like to have everything contained within Excel and not hosted on a remote server. I'm also intrigued by the capabilities of the form controls, but that's another layer about which I'm pretty clueless.

I really don't think it would be complicated for someone who knows what they're doing, I'm just at ground zero in dealing with VB.

I'm being a woman about whether or not I want to do this with VB, but it's probably the best option. Plus it's always fun to learn something new.

5/4/2009 9:21:32 PM

[Edited on May 4, 2009 at 9:35 PM. Reason : screw it, you already did]

5/4/2009 9:33:37 PM

I did this exact sort of thing as a summer intern after my freshman year of college. I nearly eliminated jobs in the department I was in when I coded up some macros to cut what was taking 3 people a few hours to do and always had a couple of mistakes to mere minutes with VBA (limited by the 56k connection to the mainframe) with no mistakes.

The problem with using the recorder and then looking at the output is it always refers to ranges by their absolute range and I've always had real problems working with the Range methods trying to loop.

The easiest way I always found was using the Cell(row,column).Value and letting your iterator live in either row or column. To find blanks and copy them to another worksheet it would be as easy as

Set sheetFrom as Worksheets('TabName')

For theRow startRow to lastRow Step 1
if sheetFrom.Cells(theRow,1).Value = 0 Then
...
...


Once you understand how to access each cell with Cells rather than trying to fuck with Range, then your scripts will look a lot more mundane and you won't need to be a VBA API guru to get useful work done.

[Edited on May 4, 2009 at 9:48 PM. Reason : .]

5/4/2009 9:47:51 PM

Quote :

"I nearly eliminated jobs in the department I was in when I coded up some macros to cut what was taking 3 people a few hours to do and always had a couple of mistakes to mere minutes with VBA"

Yeah, that's a concern for the guys whose primary task this is -- the ones who'll end up with carpal tunnel before finding a more efficient way of doing their job.

Fuck it, I'ma get paid.

--

One thing about ^'s example (and again, I don't know what I'm doing here): several of the shitty tutorials and forum posts I ran across mentioned avoiding running loops as much as possible. I took it that they were implying performance issues, so I ran a couple test For loops through my 900+ row worksheet. Nothing too complicated, but I didn't notice any significant performance hit using a loop vis a vis more hack-ish code.

I'm running Office 2003 on XP, maybe these dudes were running 97 or something. Is there anything to the don't-use-loops vibe I was picking up?

[Edited on May 4, 2009 at 10:00 PM. Reason : ]

5/4/2009 9:53:07 PM

well, as with any language, loops can be abused, and maybe it's very easy and tempting to use loops in VBA because of the natural structure of a spreadsheet (loop over rows, loop over columns inside each row, etc). If you're not careful, you can very easily build loops that iterate thousands or 10's of thousands of times just to accomplish a simple task.


And with VBA, there are several built-in functions that you can use instead of loops. For example, to find the last used row in a worksheet, you might instinctively use a loop that goes through all the rows one at a time to see if they are empty, and use a dummy counter to find the last row.

Or.... you could just assign this built-in property to a variable: ActiveSheet.UsedRange.Rows.Count


In your case, a loop could to go through the rows one at a time to see if they are blank, and copy them if they're not. If you only have 1000 rows, this shouldn't be a problem. It will happen very quickly on a decent computer. If you start looking at 10,000+ rows, well, then maybe it's time to think of another way (like AutoFilter - it just so happens, I don't really like using AutoFilter inside of a Macro)

5/4/2009 10:44:06 PM