Finding the first empty row to paste a row from another sheet

Question

I am working on a macro for copying rows for different locations to sheets specific to the locations from a master sheet.

I have everything working except finding the last row when the cell I am checking contains a '0' and shows as an empty string match. I need to either find a better way to paste to the first empty row, or to find out if the cell being checked is truly empty.

Here is the macro code:

Sub MoveDataToSheets()
'
' MoveDataToSheets Macro
' Macro written 2/25/2011 by Jim Snyder
'
    Dim rowCount As Integer, sheetIndex As Integer, LastRow As Integer
    Dim ExcelLastCell As Range

    ' Prevent screen updates from slowing execution
    Application.ScreenUpdating = False

    rowCount = ActiveCell.CurrentRegion.Rows.Count

    ' Process each row once copying row to matching location tab
    For currentRow = 1 To rowCount

        ' Determine which sheet the row goes to
        Select Case (Cells(currentRow, "B").Value)
            Case "ALTAVISTA"
                sheetIndex = 2
            Case "AN"
                sheetIndex = 3
            Case "Ballytivnan"
                sheetIndex = 4
            Case "Casa Grande"
                sheetIndex = 5
            Case "Columbus - Devices (DE)"
                sheetIndex = 6
            Case "Columbus - Nutrition"
                sheetIndex = 7
            Case "Fairfield"
                sheetIndex = 8
            Case "Granada"
                sheetIndex = 9
            Case "Guangzhou"
                sheetIndex = 10
            Case "NOLA"
                sheetIndex = 11
            Case "Process Research Operations (PRO)"
                sheetIndex = 12
            Case "Richmond"
                sheetIndex = 13
            Case "Singapore"
                sheetIndex = 14
            Case "Sturgis"
                sheetIndex = 15
            Case "Zwolle"
                sheetIndex = 16
            Case Else
                sheetIndex = 1
        End Select

        ' Only if the row cotains a valid location, copy it to location sheet
        If (sheetIndex > 1) Then
            Sheets(1).Activate                                       ' Activate the sheet being copied from
            ActiveSheet.Rows(currentRow).Copy                        ' Copy from master sheet
            Set sheet = Worksheets(sheetIndex)                       ' Designate target sheet
            Set ExcelLastCell = sheet.Cells.SpecialCells(xlLastCell) ' Find the last used row
            LastRow = ExcelLastCell.Row
            If (sheet.Rows(LastRow).Cells(LastRow, 5).Value = "") Then
                sheet.Paste Destination:=sheet.Cells(LastRow, 1)     ' Paste into first row
            Else
                sheet.Paste Destination:=sheet.Cells(LastRow + 1, 1) ' Paste in first empty row
            End If
            Sheets(1).Activate                                       ' Activate the sheet being copied from
        End If
    Next

    Application.ScreenUpdating = True
End Sub

Answer 1

Chip Pearson's site has an example of what you need. You can do this on each page prior to paste. Finding The Last Used Cell In A Range

Answer 2

For just finding the last cell that has a value in a column:

Dim LastRow As Long
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

You can then use LastRow as a standard row reference. I often use it to find the last row then add 1, which actually gives me the first empty cell in that column. Example:

Cells(LastRow + 1, 6).Value = Cells(10, 3).Value 'Or whatever value you want

The CPearson site linked above by datatoo however does list many more ways to find "last cells" if you are looking for more complicated solutions.

Edit: Just tested to be sure: this does detect 0s or nulls ( ="" ) in the column.

Answer 3

Assuming your using Excel 2003 and/or you won't have more than 65,536 on any sheet:

LastRow = Sheet.Range("A65536").End(xlUp).Row