HELP: VBA to find first non-blank cell in the row?

[Urner]

How should I write a vba code that will find the first non-blank cell in a specified row range and then copy it to another sheet?



Any suggestions?

 

[RonEmm]

Maybe something like this?

Sub FindFirstNonEmptyCell()
Dim i, j As Integer 'i = row number, j = collumn number
    For i = 1 To 2 'replace '1 and 2' to the desires range
        For j = 1 To 256 'or 16384 in Excel2007
            If Not IsEmpty(Cells(i, j)) Then
                Cells(i, j).Copy Sheets(2).Cells(i, 1) 'the first value will be copied to sheet 2
                Exit For
            End If
        Next
    Next
End Sub

Grtz,
Ron

 

[Silvrhelm]

Hi, Just select the first cell in your row range and then run this macro

Sub Macro2()
    Selection.End(xlToRight).Select
    Selection.Copy
    Sheets("Sheet1").Select  ' Replace "sheet1" with the sheet you want to copy to
    Range("A1").Select       ' Replace A1 with the cell you want to copy to
    ActiveSheet.Paste
End Sub

 

[Urner]

Thank you both.

It works great.