0

I've been writing a code to pull data into a sheet, and am trying to delete a bunch of unwanted columns. On the first range select for deleting, I keep keep getting a "method of range" error. I have tried using On Error goto - 1 and this just runs forever. However, If I change one of the parameters to $column, and run, then revert to original, the code will run fine. Is there a better way to write this to avoid the error?

Dim newvoyagerng As Range
Set newvoyagerng = sht6.Range("AN2:AN" & Rows.Count)
    With sht6
        For Each cell In newvoyagerng
            If cell.value <> "" And _
               cell.value <> "N/A" And _
               cell.value <> "0" Then   'if cells have value, check for mismatch. If mismatch, paste in sheet 12
                    If cell.Offset(0, -1) <> "0" And _
                       cell.Offset(0, -1) <> "N/A" And _
                       cell.Offset(0, -1) <> cell.value Then
                            cell.EntireRow.Copy
                            sht11.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
                    End If
            End If
        Next cell
    End With

    If sht11.Range("A2") <> "" Then
        sht11.Columns("C:G").Select  ' Error occurs on this line replacing with any variation and returning to this form let it run...
            Application.CutCopyMode = False
            Selection.Delete Shift:=xlToLeft
        sht11.Columns("E:AG").Select
            Selection.Delete Shift:=xlToLeft
        sht11.Columns("G:H").Select
            Selection.Delete Shift:=xlToLeft
    End If
Arktik
  • 17
  • 6
  • It's not easy to guess why you receive the error ... but you should read [How to avoid using select](https://stackoverflow.com/q/10714251/16578424) - fix the code accordingly - and then see if the error still happens. – Ike Jul 01 '22 at 13:17
  • ^^^^^^^^^as above. worth noting that if you do have to use select (not in this code) i think there is something about activating the sheet with the range in it first. i'm fairly sure thats how i've got round similar issues, but definitely remove the select from this – InjuredCoding Jul 01 '22 at 13:30
  • I didn't realize it would have that effect, done and done. Thanks y'all – Arktik Jul 01 '22 at 13:40

1 Answers1

0

Solution in comments, removing select.

If sht11.Range("A2") <> "" Then
        sht11.Columns("C:G").Delete Shift:=xlToLeft
        sht11.Columns("E:AG").Delete Shift:=xlToLeft
        sht11.Columns("G:H").Delete Shift:=xlToLeft
End If
Arktik
  • 17
  • 6