Find non-blank cells in range without for loop

[Prevost]

Hey There. I have a general question. I find myself using the 'For Each' loop when going through either user selected ranges or pre-defined ranges to find cells that have specific values, are blank, or are non-blank. Is there a more efficient way to accomplish this?

For example, I have a range "F2:F21" that I want to look through to find non-blank cells. When there is a non-blank cell, I am then using the information from that cell and cell offsets and exporting it into a new sheet in a new workbook. Is there a way to accomplish this without 'For Each' loops by using a built in excel function or something? I don't know which is why I am asking.

Thanks for any input!

 

[gorem]

I recently ran into a similar issue and had been prone to using "For" loops to solve most issues. Have you tried using AutoFilter or Advanced Filter in your code? That will drastically cut down on the time it takes to process large datasets, depending on your needs.

Check this out: Range.AutoFilter Method (Excel)

 

[Rick Rothstein]

Hey There. I have a general question. I find myself using the 'For Each' loop when going through either user selected ranges or pre-defined ranges to find cells that have specific values, are blank, or are non-blank. Is there a more efficient way to accomplish this?

For example, I have a range "F2:F21" that I want to look through to find non-blank cells. When there is a non-blank cell, I am then using the information from that cell and cell offsets and exporting it into a new sheet in a new workbook. Is there a way to accomplish this without 'For Each' loops by using a built in excel function or something? I don't know which is why I am asking.

It will be much easier to give you a usuable answer if you tell us the offsets from Column F that you want to copy (not export) to a new sheet in the new workbook. Also, does this new workbook already exist or is it being created "on the fly"?

 

[Prevost]

I am not sure the Autofilter method is what I am looking for unfortunately...I have my code working with the For Each loop and then copying certain offset values. The actual value of the offsets is not important. If I want to go through the range and have excel know which cells are non-blank and then those cells which are not blank, use the certain offset values (and get the row value). That's what I was sort of wondering, however I think that I don't know enough about VBA to know if this is possible or give a more specific direction. Thanks.