VBA Current region minus header and last column

[blanchard306]

I have code to minus header row from current region but I need to select current region minus header row and last column. Can anyone help?

Here is my current code

Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select


So say my current region is Rows 1 through 20 and columns A through H I would need the selection to capture rows 2 through 20 and columns A through G.

The data won't always be 1-20 and A-G sometime more rows and more columns so I need to be able to do it from current region and minus out the first row and last column.

Thanks!!

[snb]

sub M_snb()
with cells(1).currentregion
.offset(1).resize(.rows.count-1,.columns.count-1).select
end with
End sub

[blanchard306]

sub M_snb()
with cells(1).currentregion
.offset(1).resize(.rows.count-1,.columns.count-1).select
end with
End sub


THANK YOU!!!! Works perfect!!!

[Paul_Hossler]

code fragment with some commented testing statements

no error traps so if for example the selected cell has nothing else in the current region, it will error



Option Explicit
Sub test()
' ActiveSheet.Range("E5:z26").Value = 1234
' ActiveSheet.Range("M13").Select
' MsgBox Selection.Address

With Selection.CurrentRegion

' MsgBox Selection.CurrentRegion.Address
.Cells(2, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).Select
' MsgBox Selection.Address

End With
End Sub