title: Import Last Row Starting in Particular Column to Centralized Worksheet
body: <p>Looking for some help to only import rows that have been "filled in" from various worksheets to a centralized "Import" worksheet in the same workbook. Each tab in the workbook is a template, and not all rows have been filled in, but remain as a part of the template. See below for example:</p>
  
  <pre><code>Organic Fruit   Color   Quantity
  Yes     Grapes  Purple  10
  Yes     Banana  Yellow  15
  Yes     Apple   Red     4
  Yes     Orange
  No      Kiwi
  </code></pre>
  
  <p>Let's say in the above example that the "Organic" and "Fruit" columns came pre-determined, but the "Color" and "Quantity" rows were filled in by various stakeholders - some rows were not filled out in this data collection cycle, but will be in the future. In this case, I would only be interested in importing the top 3 rows, because the "Orange" and "Kiwi" rows are not currently filled out. The number of rows I am interested in compiling in the centralized "Import" tab varies based on the each tab in the workbook (i.e. it is not a standard "top 3 rows"). </p>
  
  <p>Where in my below code can I modify in order to only import the rows that have been "filled out"? There are headers in the "Import" tab. If you have any suggestions to improve the overall code too, that is much appreciated</p>
  
  <pre><code>Sub CombineDataSheets()
  
      Dim wksSrc As Worksheet
      Dim wksDst As Worksheet
      Dim rngSrc As Range
      Dim rngDst As Range
      Dim lngSrcLastRow As Long    'Src is source
      Dim lngDstLastRow As Long    'Dst is destination
  
      'Set references
  
      Set wksDst = ThisWorkbook.Worksheets("Import")
      lngDstLastRow = LastOccupiedRowNum(wksDst)
  
      'Set the initial destination range
      Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)    'edit the cells +/- forimportation into central tab in database
  
  
      'Loop through all sheets
      For Each wksSrc In ThisWorkbook.Worksheets
  
          'Make sure we skip the "Import" destination sheet!
          If wksSrc.Name &lt;&gt; "Import" And wksSrc.Name &lt;&gt; "Cover page" And wksSrc.Name &lt;&gt; "Introduction" Then
  
              'Identify the last occupied row on this sheet
              lngSrcLastRow = LastOccupiedRowNum(wksSrc)
  
              'Store the source data (start of copy area is calibrated to table insheet) then copy it to the destination range
              With wksSrc
                  Set rngSrc = .Range(.Cells(11, 2), .Cells(lngSrcLastRow, 21))
                  rngSrc.Copy Destination:=rngDst
              End With
  
              'Redefine the destination range now that new data has been added
              lngDstLastRow = LastOccupiedRowNum(wksDst)
              Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
  
          End If
  
      Next wksSrc
  
  End Sub
  
  Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long
  Dim lng As Long
  
  If Application.WorksheetFunction.CountA(Sheet.Cells) &lt;&gt; 0 Then
      With Sheet
  
         lng = .Cells.Find(What:="*", _
                            After:=.Range("S1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
      End With
  Else
      lng = 1
  End If
  LastOccupiedRowNum = lng
  End Function
  </code></pre>