List Column A cell details except Row 1

[Ducatisto]

Hello All, Sorry if this seems obvious to some of you but I have some VBA that lists all cell details in Column A. What changes do I need to do to it to make an exception of Row 1? The code I use is here;

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Sheets("Working sheet").Rows("2:65536").Clear
For Each Sheet In Sheets
    If Sheet.Name <> "Index" And Sheet.Name <> "Working sheet" Then
        For N = 1 To Sheet.Cells(65536, 1).End(x1Up).Row
            If Trim(Sheet.Cells(N, 1)) <> "" Then
                Sheets("Working sheet").Cells(65536, 1).End(xlUp).Offset(1, 0) = Sheet.Cells(N, 1)
                Sheets("Working sheet").Cells(65536, 1).End(xlUp).Offset(0, 1) = Sheet.Name
                Sheets("Working sheet").Cells(65536, 1).End(xlUp).Offset(0, 2) = N
            End If
        Next N
    End If
Next Sheet
Sheets("Working sheet").Cells(1, 1).CurrentRegion.Sort Key1:=Sheets("Working sheet").Cells(2, 1), Header:=xlYes
Application.EnableEvents = True
End Sub

Many thanks for your assistance.

 

[JackDanIce]

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As range)
Application.EnableEvents = False
sheets("Working sheet").Rows("2:" & Rows.Count).Clear
For Each Sheet In sheets
    If Sheet.Name <> "Index" And Sheet.Name <> "Working sheet" Then
        For n = 2 To Sheet.Cells(Rows.Count, 1).End(x1Up).Row
            If Trim(Sheet.Cells(n, 1)) <> "" Then
                With sheets("Working sheet").Cells(Rows.Count, 1).End(xlUp)
                    .Offset(1) = Sheet.Cells(n, 1)
                    .Offset(, 1) = Sheet.Name
                    .End(xlUp).Offset(, 2) = n
                 End With
            End If
        Next n
    End If
Next Sheet
sheets("Working sheet").Cells(1, 1).CurrentRegion.Sort Key1:=sheets("Working sheet").Cells(2, 1), Header:=xlYes
Application.EnableEvents = True
End Sub

The red 2 implies to start from row 2, compare to your code which has a 1 which means it starts from row 1

I also replaced all occurances of 65536 with Rows.Count as different versions of Excel have different maximum numbers of rows

 

[baitmaster]

welcome to the board

Use for N = 2 to ....

 

[Ducatisto]

Hi, thanks for your responses but I am still getting the 'header' row still included in the search results (via a user form) using your code. I admit I did use N = 2 originally before posting here as I thought it logical but the results I wanted never came so thought there must be something I am overseeing.