excel vba resize how can i control the resize and keep specific columns

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
113
So when I import data to my table it often has an erradict and unpredictable amount of columns which is fine except when I want to resize I would like to keep the first 8 columns of the table and delete any of the rest.

This works great below except my coulumns seem to keep the last 8 of the columns instead of the first 8.

Any guidance is appreciated.

oh and DCRPT is the "Named Table" I am using.

Code:
With DCRPT.DataBodyRange
    If .Rows.Count > 1 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count - 8).Rows.Delete
    End If
  End With
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
I would like to keep the first 8 columns of the table and delete any of the rest.
Try this:
If 'Table1' has more than 8 column then it will delete the 9th to the last column.

Code:
With ActiveSheet.ListObjects("Table1")
    n = .Range.Columns.count
        If n > 8 Then .ListColumns(9).Range.Resize(, n - 8).Delete
End With
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,823
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top