CODEWITHSUNDEEP
vb.netexcel-formula
nonprogramerguy
nonprogramerguy

Reputation: 3

How can I delete all but the last two columns in an excel sheet

I have a spreadsheet that displays the file paths to ~ 13000 files in my vault. Each row may have a different number of columns. I am really only interested in the last two columns, the folder its in and the name of the file. It is far to many rows to do manually. I need a way to either delete all but the last two columns in every row or sort it by the number of columns. I am not much of a programmer, so any help will be much appreciated.

Upvotes: 0

Views: 246

Answers (2)

JosephC
JosephC

Reputation: 929

Assuming you meant VBA not VB.NET (Please update your tags if that's the case)

This will put the last two columns (that aren't blank) into column A & B. So you can either write the code to insert the two columns or do it manually, same with deleting all the columns once the code runs.

Public Sub Test()
'Insert 2 columns: A&B

Dim LastCol As Integer
For CurRow = 2 To Sheet1.UsedRange.Rows.Count + 1
    LastCol = getLastColumn(CurRow)
    If LastCol > 2 Then
        Sheet1.Cells(CurRow, 1) = Sheet1.Cells(CurRow, LastCol - 1)
        Sheet1.Cells(CurRow, 2) = Sheet1.Cells(CurRow, LastCol)
    End If
Next

'Delete All columns except A&B
End Sub

Private Function getLastColumn(ByVal CurrentRow As Integer) As Integer
getLastColumn = -1

For ColCounter = Sheet1.UsedRange.Columns.Count + 1 To 1 Step -1
    If Sheet1.Cells(CurrentRow, ColCounter) <> "" Then
        getLastColumn = ColCounter
        Exit For
    End If
Next
End Function

Upvotes: 1

wech
wech

Reputation: 91

This seems to be more a question concerning how to use excel than vb.net, but I will try to answer it anyways:

Just click on the column headers and drag the mouse to the select all columns you want to delete, then right click on any of the selected columns and select "delete columns" from the appearing context menu.

Upvotes: 0

Related Questions