Copy Non-Blank rows in a Range and Paste

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.

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

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.

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

This is the sheet I am working with:

edit25 - ozg.xlsm

Thanks in advance for any help!

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

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?

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

Thanks again for your help!

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.

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

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

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

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.