Printable View
Greetings:
I am new to VBA, but have extensive experience with VB. Have a large worksheet, 3000 plus rows with about 15 columns. I am looking to write a macro that will find all rows where columns D thru L are blank, and if so, copy the entire row to another worksheet.
Dont really know where to start. ANy help appreciated.
thanks
john
Welcome to the forums!
Some tips.
1. Reading cell by cell can be slow for a large number of cells, so I would recommend reading the cell range of interest into an array and then process the array. To read the cell range into an array:
VB Code:
2. Be careful as to what you consider to be an empty cell - it can have a space, which appears to be empty. I use the trim function to strip out any blanks before testing.
3. Following is code that will copy/paste cells:
VB Code:
4. You can learn a lot about VBA by using the macro recorder (Tools->Macro->Record_New_Macro).
Hack is right on:ESPECIALLY if the Source sheet is "Extracted to Excel" from some other application. I like to use the Worksheet function COUNTA(aRange) = 0 as a 'blank range' test, but before I can to that, I have to scan the entire UsedRange and replace all instances where Len(aCell.Text) = 0 with a value of "" (Null String) because there is some weird cell contents that has a Text Length of Zero and no formula, but COUNTA doesn't think it is empty (???). After doing the replacement, COUNTA sees "" as empty. I also have a lot of problems with NewLines in cells - Chr(10). Sometimes you see a small box in the cell, but sometimes it just looks blank.Quote:
Be careful as to what you consider to be an empty cell
I was about to roast Hack and say that his "Array" suggestion really just sets a 'handle' to a range, but even with OPTION EXPLICIT set, it REALLY DOES copy the range to an Array of Variants. Wow! I'll have to look into this more deeply and see how to iterate through the array. Hack, can I do a COUNTA on a row of the Array? I also did a quick memory calculation based on 22 Bytes per Variant ... you'll have to have a very large worksheet before Excel chews up your physical memory. Less than half of a sheet chewed through the 122 Megs of free physical memory shown in my system monitor, and started cacheing like mad. It would take some 400-500 Megs to copy the ENTIRE contents of an ENTIRE Worksheet (65536 x 256).
Nope, don't think so. COUNTA is an Excel function that works on cells. Re memory limitations, yeah I would think there might be a tradeoff with respect to the # of cells that are read into the array.Quote:
Originally Posted by Webtest
By the way, you may remember the following thread - reading data from the cells into an array greatly improved run time.
http://www.vbforums.com/showthread.php?t=391261
P.S. Roast? I hope you were going to be kind... :)