how to get the data range of a table except the last 3 rows

[luolovepi]

Hello,
I want to get the data range of a table except the last three rows in the table. I know the code to choose the data range of the table is :

ActiveSheet.ListObjects("Table1").DataBodyRange

But how could I minus the last 3 rows?

Thank you!

Best regards,
lolo

 

[Ralajer]

lolo try this

Sub test()
    Dim tbRng As Range
    Set tbRng = ActiveSheet.ListObjects("Table1").DataBodyRange
    Set tbRng = Range(tbRng(1, 1), tbRng(tbRng.Rows.count - 3, tbRng.Columns.count))
End Sub

 

[Sektor]

Sub Except3Rows()
    Dim rng As Range
    With ActiveSheet.ListObjects("Table1").DataBodyRange
        Set rng = .Resize(.Rows.Count - 3)
    End With
End Sub

 

[luolovepi]

Thank you Ralajer!

lolo try this

Sub test()
    Dim tbRng As Range
    Set tbRng = ActiveSheet.ListObjects("Table1").DataBodyRange
    Set tbRng = Range(tbRng(1, 1), tbRng(tbRng.Rows.count - 3, tbRng.Columns.count))
End Sub

 

[luolovepi]

Thanks Sektor!
Did you see the reply in another thread of mine ?
Sorry to trouble you~~~

Sub Except3Rows()
    Dim rng As Range
    With ActiveSheet.ListObjects("Table1").DataBodyRange
        Set rng = .Resize(.Rows.Count - 3)
    End With
End Sub

 

[Sektor]

No. Gimme the link to this thread.

 

[luolovepi]

Hi Sektor,
Are there any problem with code below?

    With Pr.ListObjects("Table1")
        Set PrPartNumberRange = .ListColumns(4).DataBodyRange
        Set tableContentRange = PrPartNumberRange.Offset(1, 0). _
                                Resize(PrPartNumberRange.Rows.count - 4, _
                                PrPartNumberRange.Columns.count - 1). _
                                SpecialCells(xlCellTypeVisible)
    End With

The link of another question is as below:
http://www.mrexcel.com/forum/showthread.php?t=558699

Thanks a lot!!!

No. Gimme the link to this thread.

 

[Sektor]

You get one column and resize it by subtract 1. You get 0 and error.

 

[Sektor]

You get 0 by red color.

With Pr.ListObjects("Table1")
        Set PrPartNumberRange = .ListColumns(4).DataBodyRange
        Set tableContentRange = PrPartNumberRange.Offset(1, 0). _
                                Resize(PrPartNumberRange.Rows.count - 4, _
                                [B][COLOR="Red"]PrPartNumberRange.Columns.count - 1[/COLOR][/B]). _
                                SpecialCells(xlCellTypeVisible)
    End With

 

[luolovepi]

No need to see this question. already solved!

Thank you !!!

Best regards,
lolo^-^

Hi Sektor,
Are there any problem with code below?

    With Pr.ListObjects("Table1")
        Set PrPartNumberRange = .ListColumns(4).DataBodyRange
        Set tableContentRange = PrPartNumberRange.Offset(1, 0). _
                                Resize(PrPartNumberRange.Rows.count - 4, _
                                PrPartNumberRange.Columns.count - 1). _
                                SpecialCells(xlCellTypeVisible)
    End With

The link of another question is as below:
http://www.mrexcel.com/forum/showthread.php?t=558699

Thanks a lot!!!