Copy Non-Blank rows in a Range and Paste

Dezine · Jan 21st 2021

Hi Everyone,

I am trying to copy and paste non-blank rows. I have tried two sets of code found on another thread but both have issues I am too ignorant to resolve on my own.

This one works pretty well but I have some hidden rows that it causes to unhide. Also if the columns in the range that is being copied from are hidden, it copies all the blank rows.

Code

Sub PopulatePartsList()
Application.ScreenUpdating = False
With Sheets("Incoming").Range("V40:AF417")
.AutoFilter
.AutoFilter Field:=4, Criteria1:="<>" 'if Description (4th column) is blank, dont copy
.Offset(0, 0).Copy 'copy starting at the top left cell of the range V40:AF417
End With
With Sheets("Incoming")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'paste values 1 row and 2 columns over from the last last non-blank cell
End With
With Sheets("Incoming").Range("V40:AF417")
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Display More

This one I am way to dense to really understand but it is only copying the first row. This code works a lot faster and doesn't have the other drawbacks I mentioned for the first set of code, so it would be nice if it could be made to work.

Code

Sub CopyRangeOzgEdit25() ' only copies first row
Dim x, y(), i As Long, ii As Long
x = Sheets("Incoming").[V40:AF417]
For i = 1 To UBound(x, 1)
If x(i, 1) <> "" Then
ReDim Preserve y(1 To 6, 1 To i)
For ii = 1 To 6
y(ii, i) = x(i, ii)
Next
Else: Exit For
End If
Next
With Sheets("Incoming")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 2).Resize(UBound(y, 2), 6) = Application.Transpose(y)
End With
End Sub

Display More

This is the sheet I am working with:

edit25 - ozg.xlsm

Thanks in advance for any help!

**royUK** · Jan 22nd 2021

What do you mean by hidden rows? AutoFilter hides the unwanted rows, so AutoFilter at the end of the code will unhide the rows.

Are they within the data that is being filtered?

Are you copying the cells and pasting special on the same sheet?

Code

Sub PopulatePartsList()
''/// copy data to another sheet based on first 3 letters of entry
Dim rData As Range
' On Error GoTo err_quit
Application.ScreenUpdating = 0
With Sheets("Incoming")
''/// check if AutoFilter is on, if not switch on
If Not .AutoFilterMode Then .Range("A1").AutoFilter
''/// add range to filter
Set rData = .Range("V40:AF417")
''/// autofilter data
rData.AutoFilter Field:=4, Criteria1:="<>"
''/// copy filtered data to destination sheet
.AutoFilter.Range.Resize(, rData.Columns.Count).Copy
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'paste values 1 row and 2 columns over from the last last non-blank cell
End With
err_quit:
Application.ScreenUpdating = 1
End Sub

Display More

Dezine · Jan 22nd 2021

Hi Roy,

Thanks so much for your help!

What do you mean by hidden rows? AutoFilter hides the unwanted rows, so AutoFilter at the end of the code will unhide the rows.

- The sheet has some rows and columns hidden that have nothing to do with this code. Those rows and columns need to still be hidden after the code is finished.

Are they within the data that is being filtered?

- Yes they are but to be clear, I don't have any filters on this sheet. The only filters are the ones that the code creates and then removes. When the code removes the filters at the end it is causing everything to be unhidden including the rows that were hidden before the code was run. For this reason, I think the AutoFilter method may not be a good fit for this scenario. I was thinking the second set of code I posted had more promise but I just don't know how to adapt it to my scenario.

Are you copying the cells and pasting special on the same sheet?

- Yes. The thread I got the code from was intended to copy from one sheet and paste to another so maybe there is some unnecessary referencing of the sheet in there but I was just trying not to break the code.

I tried the code you posted above and got an error about merged cells. There are no merged cells in the range that we are copying from or pasting to. While I was investigating, I noticed it applied the filters starting at A1 instead of V38. I changed line 12 from A1 to V38. After that change the code runs with no errors but only copies the title row V38:AF38 I also notice that it left the filters applied which would not be acceptable but as I mentioned above, I think that is going to have the effect of unhiding all the rows including the ones that were hidden before the code was run.

This is the sheet I am working with:

edit26 - ozg.xlsm

Thank you again for your help!!

davesexcel · Jan 23rd 2021

Using column Q to indicate the non blank cells, you can loop through column V to find values.

Code

Sub GetData()
Dim rng As Range, c As Range, LstRw As Long
Set rng = Range("Q40:Q430").SpecialCells(xlCellTypeConstants, 23).Offset(, 5)
Application.EnableEvents = False
For Each c In rng.Cells
If c <> "" Then
LstRw = Cells(Rows.Count, "C").End(xlUp).Row + 1
Cells(LstRw, "C") = c
Cells(LstRw, "F") = c.Offset(, 3)
Cells(LstRw, "K") = c.Offset(, 8)
Cells(LstRw, "M") = c.Offset(, 10).Value
End If
Next
Application.EnableEvents = True
End Sub

Display More

Dezine · Jan 25th 2021

Thanks so much Dave! This works very well. I have been trying to modify your code just slightly to look at column Y instead of V to find values but I have not been able to figure out how to do that without breaking it. Sometimes the item may not have a part # (column V) but it would always have a description (column Y).

Also, just out of curiosity what does the "23" in this line do?

Code

Set rng = Range("Q40:Q417").SpecialCells(xlCellTypeConstants, 23).Offset(, 5)

Thanks again for your help!

davesexcel · Jan 25th 2021

Quote from Dezine

.Offset(, 5)

Means 5 columns from column Q.

Select Q1 and hit the right arrow 5 times to V1

**Carim** · Jan 25th 2021

Quote from Dezine

Also, just out of curiosity what does the "23" in this line do ?

Should you need to fully understand the SpecialCells Method ... see

https://www.ozgrid.com/VBA/special-cells.htm

and the Number 23 is a shortcut to replace the 4 possible XlSpecialCellsValue constants

Code

SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlTextValues + xlNumbers)

detail :

xlErrors +16 xlLogical +4 xlTextValues +2 xlNumbers +1 ... or a total sum of 23 ...

Hope this helps

Dezine · Jan 25th 2021

Thanks Dave and Carim!

Is there an easy way to make it use column Y instead of column V to determine if the line should be ignored?

I tried changing the offset 5 to 8 but that also caused it to start capturing the data from column Y instead of column V. I am hoping to capture the data from column V:AF but use column Y to determine if the row should be included or left out.

**Carim** · Jan 25th 2021

Thanks for your Thanks ...

Not sure to understand your latest request ...

What Dave has designed for you is :

' Using column Q to indicate the non-blank cells... AND Loop through column V to find values ...

Set rng = Range("Q40:Q430").SpecialCells(xlCellTypeConstants, 23).Offset(, 5)

What is your actual objective ...?

Dezine · Jan 25th 2021

Well I was a little confused about that actually. Dave said it was using Q to indicate the non-blank cells (and now you said the same thing) but when I test the code, it is actually using V.

The objective is to copy the rows in range V40:AF417 if column Y is not blank.

- the rows meeting this description should be pasted starting on C428 (the last row on the sheet)

*note columns Q:T are sort of like a master list of all parts and columns A:AF are the list of parts actually used.

This is the sheet with Dave's code inserted ===> edit27 - ozg.xlsm

**Carim** · Jan 25th 2021

Well...

If you do need to test Column Y ...

the instruction should modified to be :

Set rng = Range("Y40:Y417").SpecialCells(xlCellTypeConstants, 23)

But, once a given cell in this column complies with your selection :

Which cells are the Source ... and which cells are the Destination ....???

**Carim** · Jan 25th 2021

From the decoding of your message ...

Source is made of 11 Columns starting in Column V

Destination is made of 11 Columns starting in Column C

See attached test file

Hope this will help

Dezine · Jan 25th 2021

Thanks Carim,

I tried changing that line to:

Set rng = Range("Y40:Y417").SpecialCells(xlCellTypeConstants, 23).Offset (, -3)

When I run it, I get an error on that line saying "no cells were found". I tried some other offsets and no offset at all but always get the same error.

The cells that are the source to copy from are V40:AF417

The cells that are the destination to paste to are C428:M428 and down

Dezine · Jan 25th 2021

Sorry Carim, I just noticed your last message. When I ran your code I got an error saying "No cells were found"

**royUK** · Jan 26th 2021

One important thing to remember when usig SpecialCells is that they will cause an error if no cells matching the criteria exist.I would always use an error handler

Code

On Error Resume Next
SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlTextValues + xlNumbers)
On Error Goto 0

**Carim** · Jan 26th 2021

Quote from Dezine

Sorry Carim, I just noticed your last message. When I ran your code I got an error saying "No cells were found"

Difficult to guess what is actually in front of your eyes ....

The test file you posted does not contain data ... which prevents anyone from ... testing ...

Have your own test ... with your own test file with the following

Code

Sub PopulatePartsListV3()
Dim rng As Range, c As Range, LstRw As Long
Set rng = Range("Y40:Y417")
Application.EnableEvents = False
For Each c In rng.Cells
If c <> "" Then
LstRw = Cells(Rows.Count, "C").End(xlUp).Row + 1
Range(Cells(LstRw, "C"), Cells(LstRw, "M")) = Range(Cells(c.Row, "V"), Cells(c.Row, "AF"))
End If
Next c
Application.EnableEvents = True
End Sub

Display More

Hope this will help

**Carim** · Jan 26th 2021

Thanks a lot for the Like

Assume it means you have managed to solve your initial query ...

Dezine · Jan 26th 2021

Roy - Thanks so much for for your help. I added your code to the code Carim shared inside the sheet he uploaded and I got this message box but no data was copied to the destination cells.

---------------------------

Microsoft Excel

---------------------------

$Y$40,$Y$46,$Y$52,$Y$58,$Y$64,$Y$70,$Y$76,$Y$82,$Y$85:$Y$86,$Y$90:$Y$91,$Y$104,$Y$160,$Y$171:$Y$181,$Y$186:$Y$191,$Y$217,$Y$222:$Y$227,$Y$250,$Y$252:$Y$254,$Y$286:$Y$289,$Y$306,$Y$309:$Y$312,$Y$330,$Y$351,$Y$396

---------------------------

OK

---------------------------

Carim - Thanks again for all your help on this! I apologize that I have not described things well enough and caused confusion. The sheet I provided only has labels removed. Everything that this code is referencing is intact. Any code that I run works exactly the same as it will on the sheet I am going to move it to eventually. I am only testing on the sheet I provided. Are you getting different results in your testing than I am describing?

When I run the code from your last post, there are no errors but no data is copied to the destination cells.

**Carim** · Jan 27th 2021

Hello,

Attached is the latest macro copied in your latest file ...

Hope this will help

**Carim** · Jan 27th 2021

Once you tested your button and the macro ... feel free to share your comments ...

Share