Copy the range below to another location, but ignoring the blank rows

[Guinaba]

Hi guys,

I need to copy the range below to another location, but ignoring the blank rows. Any suggestions? The blank rows vary depending whether the formulas in there bring or not values.

Roast Birds			State variance +/-10%
	This week at 596t, -110t down vs DP. 7t drop from yesterday	QLD	NSW	VIC	SA	WA	TAS	NT
	84%	85%	86%	83%	87%	80%	77%
Woolworths Deli: Top 5 positive and negative variances greater or less than 3t		State variance +/-10%
	Positive Variances	Variance to DP	QLD	NSW	VIC	SA	WA	TAS	NT
	1511300​	BREAST FILLETS 12KG	6t above DP	127%	91%	102%	110%	104%	78%	88%	4t drop from yesterday.
	3415800​	F/RANGE CHICKEN 1.45KGX8	6t above DP	106%	104%	113%	100%	112%	90%	100%	2t drop from yesterday.
	Negative Variances		QLD	NSW	VIC	SA	WA	TAS	NT
	1364100​	DRUMSTICKS 12KG	12t below DP	93%	91%	95%	94%	89%	80%	81%	8t drop from yesterday.
Woolworths Meat: Top 7 positive and negative variances greater or less than 3t
	Positive Variances		QLD	NSW	VIC	SA	WA	TAS	NT
	1820500​	F/RANGE THIGH FILLET RWX12	3t above DP	115%	117%	108%	117%	109%	93%	110%
	3159200​	LE/HERB BLESS BFLYCHICKEN RWX6	3t above DP	91%	105%	105%	105%	180%	165%	92%
	Negative Variances		QLD	NSW	VIC	SA	WA	TAS	NT
	1593500​	BREAST FILLETS RWX8	10t below DP	94%	95%	92%	90%	89%	74%	104%	1t drop from yesterday.
	1557300​	F/RANGE BREAST FILLET LGE RWX8	6t below DP	87%	90%	85%	93%	96%	67%	90%	2t drop from yesterday.

 

[SQUIDD]

Hi

So since you haven’t had a response on this I could think of a couple of ways forward for you.

1, create a loop to check if row is empty. If empty move on etc. otherwise copy the row.

2, use a helper column with formula to determine if there is data in the row. Then filter the range by the helper column, copy and paste the filtered data.

Am happy to assist with either of the above.
My response may bump your post for someone else to make suggestions.

Upon posting your reply , if you could supply the row and column at where your data starts and possibly ends.

Also please tell us the location you want the data to go to.

Dave.

 

[Guinaba]

Thanks @SQUIDD for anwering! You gave me a really good suggestion and I ended up adopting both
1) create a loop to check if row is empty
2) use a helper column with formula to determine if there is data in the row

So in my range now I have the first col. having 1 or 0. If 1 then copy, 0 delete the entire row, however not sure why my code is not deleting the rows with 0:

Dim c As Integer
Dim r As Integer
    
   'loop though the array
    For c = LBound(CurrWkArr4, 2) To UBound(CurrWkArr4, 2)
        For r = LBound(CurrWkArr4, 1) To UBound(CurrWkArr4, 1)
            If CurrWkArr4(r, 1) = 1 Then
               CurrWkWB.Worksheets("Test").Cells(r, c).Value = CurrWkArr4(r, c) 'Copy the data from Arr back to sheet (Test)
               Else
               CurrWkArr4(r, c).EntireRow.Delete ' Delete the entire row
            End If
        Next r
    Next c

 

[SQUIDD]

Hi.

I’m not at my pc right now.
But in my experience, when deleting rows I have always changed my for loop to delete rows from bottom to top.

So for example

For a = 100 to 1 step -1

Will be at my pc in a couple of hours.

Also, cannot see you have specified CurrWkArr4 as anything ?

Dave.

 

[SQUIDD]

Also would the below not delete the row by row number ?

Rows(r).EntireRow.Delete

 

[Guinaba]

Also would the below not delete the row by row number ?

Rows(r).EntireRow.Delete

Hi @SQUIDD,

Thanks for tip about the reverse loop. Amost there , rather than looping and deleting the rows when copying, I've found that it is faster just copying the entire range and after that delete the rows flagged with "N".

However, not sure what is wrong with my logic, because my reverse loop in deleting only the last empty row in the range and stop looping afterwards, it should keep looping backwards...

    For r = LastRow To Worksheets("EmailFormat").Range("B2") Step -1    
        If Worksheets("EmailFormat").Range("B" & r) = "N" Then
           Worksheets("EmailFormat").Range("B" & r).EntireRow.Delete
        End If 
    Next r

 

[SQUIDD]

Hi

So in your 1st line of code

For r = LastRow To Worksheets("EmailFormat").Range("B2") Step -1

with this bit

Worksheets("EmailFormat").Range("B2")

You are looping down to the value of this cell?

I would suggest the below on the assumption you data starts at row 2 and you have headers.

For r = LastRow To 2 Step -1
    If Range("'EmailFormat'!B" & r) = "N" Then Range("'EmailFormat'!B" & r).EntireRow.Delete
Next r

Dave

 

[Guinaba]

Hi

So in your 1st line of code

For r = LastRow To Worksheets("EmailFormat").Range("B2") Step -1

with this bit

Worksheets("EmailFormat").Range("B2")

You are looping down to the value of this cell?

I would suggest the below on the assumption you data starts at row 2 and you have headers.

For r = LastRow To 2 Step -1
    If Range("'EmailFormat'!B" & r) = "N" Then Range("'EmailFormat'!B" & r).EntireRow.Delete
Next r

Dave

Hi

So in your 1st line of code

For r = LastRow To Worksheets("EmailFormat").Range("B2") Step -1

with this bit

Worksheets("EmailFormat").Range("B2")

You are looping down to the value of this cell?

I would suggest the below on the assumption you data starts at row 2 and you have headers.

For r = LastRow To 2 Step -1
    If Range("'EmailFormat'!B" & r) = "N" Then Range("'EmailFormat'!B" & r).EntireRow.Delete
Next r

Dave

Awesome @SQUIDD !! Thanks Dave! It worked like a charm!!

 

[SQUIDD]

Great. Glad it’s all working for you.

You nearly got there yourself.

Good job.

Dave.