Posts by Dezine

Thanks so much Carim! That works perfectly!

Hi Carim!

Thanks so much for your help! I made a slight modification because I forgot to mention the start and end dates have to be counted, so for example if the date out is the 1st and the date in is the second, that would be counted as 2 days not one. I also changed I to use a static month because the more I thought about this the more I realized this will be necessary when looking back. Sorry I didn't have that part thought out fully before requesting help.

I noticed two issues:

1. The rows that have a Date In filled out are not matching the expected result.

2. I definitely messed up when trying to adapt this formula to a prior month.

Thanks again for your help so far and sorry for not having all my thoughts in order before making the original post.

Hi everyone!

I am trying to count the number of days in a range that fall in the current month. If Date In is blank then todays date should be used for Date In. I have tried several pretty complicated formulas I found that started with very similar requests but for some reason I cannot adapt them to my sheet. This is a screenshot of the relevant cells is shown below. I also attached a sample spreadsheet. If it helps, a helper cell can be added to store the current Month. Any help would be greatly appreciated.

Sounds like very good advice and there is no reason why I could not have done that in this workbook. Thanks again for the lessons!

Wow I never would have figured that out lol. Thanks again for your help and the lesson!

PERFECT!!! Thanks so much Carim!!

You have done so much that I hate to ask just out of curiosity but the only change I see between the last two versions of code were the column reference in this line:

LstRw = Cells(Rows.Count, "F").End(xlUp).Row + 1

But it doesn't seem like that line is intended to impact the inclusion/exclusion criteria. I totally understand if you want to move on with your life but would be interested in learning how each line is impacting the results.

Either way, thanks again!!!

Posting working code here for posterity:

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, "F").End(xlUp).Row + 1
            Range(Cells(LstRw, "C"), Cells(LstRw, "M")).Value = Range(Cells(c.Row, "V"), Cells(c.Row, "AF")).Value
        End If
    Next c
 Application.EnableEvents = True
End Sub

Thanks again Carim and sorry I am not adequately describing the symptoms. The row is being excluded if column Y is blank which is great but rows are also being excluded if only column V is blank. I removed the contents of V223. Y223 still has data in it but row 223 is not being copied.

Test edit29 - ozg.xlsm

Thanks so much for sticking with me Carim!

Now it looks like if either column V or Y is blank, the row will not be copied. My hope was that it would only skip the ones that are blank in row Y.

In the sheet below, I deleted the contents of cell V223 and cell Y224 so I could see which column was being referenced.

Test edit29 - ozg.xlsm

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.

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

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

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

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!

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

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!

Thanks Gizmo! I understand now why it is happening. I will create a new thread asking for help accomplishing the task.

Hi Everyone,

For some reason this code is causing all rows to unhide. It does exactly what I need besides that little hiccup. Should I use a different method? Thanks in advance for any help.

Option Explicit
Sub PopulatePartsList()
 'Application.ScreenUpdating = False
 With Sheets("Incoming").Range("V40:AF417")
 .AutoFilter
 .AutoFilter Field:=4, Criteria1:="<>" 
 .Offset(0, 0).Copy 
 End With
 With Sheets("Incoming")
 .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False 
 End With
 With Sheets("Incoming").Range("V40:AF417")
 .AutoFilter
 End With
 'Application.ScreenUpdating = True
End Sub