copy a column leave out blank rows

[g48dd]

Excel 2003: I have a SS that is about 5000 rows, I have a column N that tracks mistakes. There are not very many mistakes so out of 5000 rows there is usually 10 rows that I have to worry about but these 10 are different every time. If I hold control and scroll the column and highlight each mistake, then click copy... when I paste it will paste all of the info together in say 10 rows even though at times some of these rows are separated by 100 blank rows. What I would like is a macro that looks for any data in column N, That data is going to look like this L600345, then maybe 100 blank rows then it will see L1000353, then maybe 20 blank rows and it will come across L870750 and so on and I need it pasted into column A starting at row 10 on sheet3!

Ken

 

[VoG]

Try

Sub ColumnN()
Dim LR As Long
LR = Range("N" & Rows.Count).End(xlUp).Row
Range("N1:N" & LR).SpecialCells(xlCellTypeConstants, xlTextValues).Copy Destination:=Sheets("Sheet3").Range("A10")
End Sub

 

[g48dd]

I am getting an error that says "No Cells were found" then in Debug it highlights the line beginning with: RANGE. Now I lied it was not column N it turns to be column O but that should be no big deal I just changed N to O, and it was not sheet3 it was sheet1. I have many, many tabs they are all named.... the sheet I am running the macro on is named Main I still want it to go to sheet1 as that is my test sheet. I think I know why it did not find anything when I know there are mistakes on the sheet for it to find. In column O the way the mistake shows up is by this formula

=IF($N242=37,$A237,IF($N246=37,$A237,""))

The formula is looking at column N to see if the number 37 shows up, that is the index number of pale blue, if that shows up then the web query did not process for some reason and it should return a number to column O, that number is the name of an on line printer, I want the name of every printer I have that the Web Query did not download so that I can investigate and I find out why. It looks for 37 because all my web queries are marked where the query is by the background color Pale Blue if the query takes place the background color will be removed and the formula will return nothing to column O but if the download fails the cell where the query is will remain Pale Blue and the formula in column N will return the name of my printer in column O but I am thinking this is the problem and the reason why your script does not work? Its the only thing I can think of. I didn't mean to lie, I wasn't thinking... I apologize

 

[VoG]

Try

Sub ColumnO()
Dim LR As Long, FR As Long, i As Long
FR = 10
With Sheets("Main")
    LR = .Range("O" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("O" & i)
            If .Value <> "" Then
                .Copy Destination:=Sheets("Sheet1").Range("A" & FR)
                FR = FR + 1
            End If
        End With
    Next i
End With
End Sub

 

[g48dd]

OK... Run-Time error "13": type mismatch and this is highlighted

If .Value <> "" Then

would it be easier if I attached part of the sheet?

Ken

 

[VoG]

I'm guessing that you have some error values in that column. Try

Sub ColumnO()
Dim LR As Long, FR As Long, i As Long
FR = 10
With Sheets("Main")
    LR = .Range("O" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If Not IsError(.Range("O" & i)) Then
            With .Range("O" & i)
                If .Value <> "" Then
                    .Copy Destination:=Sheets("Sheet1").Range("A" & FR)
                    FR = FR + 1
                End If
            End With
        End If
    Next i
End With
End Sub

 

[taurean]

Maybe this:

Public Sub CopyErrCells()
Dim lLR As Long
lLR = Range("O" & Rows.Count).End(xlUp).Row
Range("A10:O" & lLR).AutoFilter Field:=15, Criteria1:="<>"
Range("A10:O" & lLR).SpecialCells(xlCellTypeVisible, xlTextValues).Copy
ActiveSheet.Paste Destination:=Sheet1.Range("A" & Rows.Count).End(xlUp)(2)
Application.CutCopyMode = False
End Sub