none
iterate thru cells, if blank, move to next.... RRS feed

  • Question

  • Evening all.

    I have a worksheet that has several rows of data.

    The source data I'm interested in is dates in column B.

    I want to iterate through each row, in column B, looking for dates. If there's a date, I want to copy a range of cells in those rows where there's a date.

    If there's no dates, I want to check subsequent rows, until I reach the end of the range.

    How would I accomplish this?

    TYIA.

    Saturday, December 17, 2016 2:34 AM

Answers

  • You haven’t said what’s in the range or what are the range’s boundaries.

    Let me presume that in the first row you have column names, and that at least one column have all values entered. Then you can use something like this, or you have to determine range’s boundaries some other way.

    Anyway, add another module and put this macro in. Making it public, enables you to call it form macro list.

    Public Sub CopyRowsWithDatesToAnotherSheet()
    Dim wsS As Worksheet, wsD As Worksheet
    Dim lRowS As Long, lRowD As Long, lRowLast As Long
        Set wsS = ThisWorkbook.Worksheets("Source")
        Set wsD = ThisWorkbook.Worksheets("Destination")
        'copying columns names
        wsS.Range("a1").EntireRow.Copy
        wsD.Range("a1").PasteSpecial xlPasteAll
        'determining range boundaries
        lRowLast = wsS.Range("a1").CurrentRegion.Rows.Count
        'copying rows with dates in them
        lRowD = 2
        For lRowS = 2 To lRowLast
            If wsS.Range("b" & CStr(lRowS)).Value2 <> "" Then
                If IsDate(wsS.Range("b" & CStr(lRowS)).Value) Then
                    wsS.Range("a" & CStr(lRowS)).Copy
                    wsD.Range("b" & CStr(lRowD)).PasteSpecial xlPasteAll
                    lRowD = lRowD + 1
                End If
            End If
        Next lRowS
    End Sub
    

    Saturday, December 17, 2016 3:34 AM

All replies

  • You haven’t said what’s in the range or what are the range’s boundaries.

    Let me presume that in the first row you have column names, and that at least one column have all values entered. Then you can use something like this, or you have to determine range’s boundaries some other way.

    Anyway, add another module and put this macro in. Making it public, enables you to call it form macro list.

    Public Sub CopyRowsWithDatesToAnotherSheet()
    Dim wsS As Worksheet, wsD As Worksheet
    Dim lRowS As Long, lRowD As Long, lRowLast As Long
        Set wsS = ThisWorkbook.Worksheets("Source")
        Set wsD = ThisWorkbook.Worksheets("Destination")
        'copying columns names
        wsS.Range("a1").EntireRow.Copy
        wsD.Range("a1").PasteSpecial xlPasteAll
        'determining range boundaries
        lRowLast = wsS.Range("a1").CurrentRegion.Rows.Count
        'copying rows with dates in them
        lRowD = 2
        For lRowS = 2 To lRowLast
            If wsS.Range("b" & CStr(lRowS)).Value2 <> "" Then
                If IsDate(wsS.Range("b" & CStr(lRowS)).Value) Then
                    wsS.Range("a" & CStr(lRowS)).Copy
                    wsD.Range("b" & CStr(lRowD)).PasteSpecial xlPasteAll
                    lRowD = lRowD + 1
                End If
            End If
        Next lRowS
    End Sub
    

    Saturday, December 17, 2016 3:34 AM
  • Hi SteveDB1,

    please try to refer the suggestion given by IvicaNesic.

    it looks like the sample code example given by IvicaNesic give you an idea to fulfill your requirement.

    so you can refer the code and make changes as per your own requirements.

    if you think that suggestion given by IvicaNesic can solve your issue then mark his suggestion as an answer.

    if still have a problem to implement the suggestion then let us know about that. we will try to suggest you further to solve your issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 19, 2016 1:16 AM
    Moderator
  • Don’t bother Deepak. If he really needed the answer, he would have explained their problem with more details. Just archive the thread.

    Friday, December 30, 2016 3:54 AM
  • Don’t bother Deepak. If he really needed the answer, he would have explained their problem with more details. Just archive the thread.

    Hi Ivica.

    I've been quite busy these past two weeks, and when I did get to this last weekend, I found that I wasn't clear on how to explain the issues I was having. I'm not freed up, and will be dealing with this over the next few days.

    I.e., it's got nothing to do with you, or the answer you provided.

    Friday, December 30, 2016 4:12 AM
  • Hi SteveDB1,

    you had mentioned that,"I found that I wasn't clear on how to explain the issues I was having. I'm not freed up, and will be dealing with this over the next few days. "

    you can again start to describe the issue. we will again try to look in to the issue and try to solve it.

    if you are not clear how to describe it then you can show us in pictures or can give an example that can give us an idea.

    if that not work then you can try to post the sample file.

    we will try to reproduce the issue with that.

    I can understand that you are busy with your work but this thread is still active on our side. so we try our best to provide a suitable suggestion to you that can solve your issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 30, 2016 5:23 AM
    Moderator