Copy to end of range but ignore blanks

[Mr2017]

Hi

Is it possible to copy data in a range but ignore the blanks?

I usually use this code to copy data in a range but it’s copying rows where there a formulas that return blank values, which I don’t want it to:

Range("A1", Range("A1").End(xlToRight).End(xlDown)).Copy
To illustrate the issue, please see the simple example below:

I have two sheets.

In Sheet 1, the values 1, 2, and 3 are in cells A1, A2, and A3, respectively:

1

2

3

<tbody>
</tbody>

In Sheet 2, cells A1 and B1 have the titles “Number” and “Letter,” respectively.

Cell A2 in Sheet 2 has this formula: =IF(Sheet1!A1="","",Sheet1!A1)

And cell B2 in Sheet 2 has this formula: =IF(Sheet1!B1="","",Sheet1!B1)

Both formulas have been dragged down to row 10 of each column.

So the result looks like this – ie cells B2:B4 have values because the formulas in Sheet 2 find values for them from Sheet 1.

Number	Letter
1
2
3

<tbody>
</tbody>

The problem is that when this Sub is run, it copies everything in the range, including the rows with the blank formulas!

Sub CopyNonBlankRowsInRange()


Sheet2.Activate


Range("A1", Range("A1").End(xlToRight).End(xlDown)).Copy


End Sub

Is there a way for it to stop at the last populated cell? ie the row with the value “3” in Sheet 2 in this example?

I'd prefer to avoid using a loop, as they can take forever, when there's lots of data.

TIA

 

[njimack]

This may work for you - change the formula so that it displays an error (which you can convert to white text using Conditional Formatting) rather than a null-length string, e.g
=IF(Sheet1!A1="",NA(),Sheet1!A1)

You can then use SpecialCells to exclude formulae that return an error:

Range("A1", Range("A1").End(xlToRight).End(xlDown)).SpecialCells(xlCellTypeFormulas, 7).Copy

 

[Mr2017]

Hi Neil

Thanks for your response.

It works, however, the real data has dozens of formulas which would all need to be amended, which would take a long time.

Is there no way to do it using VBA only?

 

[njimack]

Hi Neil

Thanks for your response.

It works, however, the real data has dozens of formulas which would all need to be amended, which would take a long time.

Is there no way to do it using VBA only?

Can't you use Find+Replace to amend the formulae?
You could use VBA to loop through each cell and examine the result of each formula. This would take much longer to run, though.

 

[Mr2017]

The formulae are all very different.

And because there are dozens of formulae in the file , it would take ages to go in and amend all the different types!

 

[njimack]

Just press Ctrl+F and replace ,"", with ,NA(),

 

[Mr2017]

The file is quite large though, and I'd be reluctant to add the additional conditional formatting you've suggested to it because it would make it larger and slow down the file overall.

I appreciate what you've suggested, but if you know of any way to do it without changing the formulae and the formatting, it would be greatly appreciated. I'm keen not to increase the size (and therefore performance) of the file.

Thanks in advance.