Excel Macro Help-Find specific blank columns in rows

[jhsalvin]

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

[VBAhack]

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:

Dim array1
array1 = Sheets("Sheet3").Range("A1:B3")

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:

Sheets("Sheet3").Range("A1:Q1").Copy Sheets("Sheet1").Range("G1") 'G1 is destination

4. You can learn a lot about VBA by using the macro recorder (Tools->Macro->Record_New_Macro).

[Webtest]

Hack is right on:

Be careful as to what you consider to be an empty cell

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.

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).

[VBAhack]

Hack, can I do a COUNTA on a row of the Array?

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.

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...