Selecting certain rows

Hi,

You can use,

Range("B6:B160").Select
    Selection.Copy

or, u can use. this will select the cells from B6 until it finds the nonblank cell in the sheet.

    Sub try()
      
    
    Dim rLastCol As Range
     
    Range("B6").Select
    Set rLastCol = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious)
     
    Application.Goto Range(ActiveCell, rLastCol)
    End Sub

...and if I misinterpreted the original perhaps. Create the range name myrange as before

Sub CopyRows()
    Dim x As Long
    x = InputBox("Enter the number of columns to copy") - 1
    Range("B6:B150").AutoFilter Field:=1, Criteria1:="<>"
    Range(Range("B6:B150"), Range("B6:B150").Offset(0, x)).SpecialCells(xlCellTypeVisible).Copy
    Range("myrange").PasteSpecial (xlPasteValues)
End Sub