vba to maniplute columns error

[Gerry:::::)]

Hi,

I am having trouble with my macros as they only run for a small amount time, before falling over... reasons being the IT DB admins keep changing the system reports columns by adding (mostly adding extra )or deleting columns, I export these reports to CSV Excel file then run my macros to delete and manipulate not needed columns and data.

I need a way of amending my existing code to attempt to full proof this so as even if IT insert, move or remove columns (as they do with there patches/updates) my macro/s will still run without falling over.

So currently my macros does something similar to the following simple example

 ' I really need a way to find the specific columns rather than the below if statement which just checks if the column contains the specific value I wish to manipulate.


If Range("O2").Value = "VIDEO" And Range("P2").Value = "EVENT" Then

    Application.CutCopyMode = False
    Columns("O:P").Select
    Selection.Delete Shift:=xlToLeft

end if

 

[vcoolio]

Hello Gerry,

Quite some time ago, I helped a Poster elsewhere with a similar request to yours. The Poster had a regular data dump into an Excel file with lots of useless data in columns that needed to be deleted. The code is as follows:-

Sub DeleteColumns()

Dim lCol As Long, ar As Variant
Dim i As Integer, x As Long
Dim sh As Worksheet: Set sh = Sheets("List")

lCol = Cells(1, Columns.Count).End(xlToLeft).Column
ar = sh.Range("H_Names")

For i = 1 To UBound(ar)
      For x = lCol To 1 Step -1
                  If Cells(1, x) = ar(i, 1) Then
                  Cells(1, x).EntireColumn.Delete
                  End If
            Next x
      Next i
     
End Sub

In this instance, the heading names of the columns that were to be deleted were placed in Column A of a separate sheet name List. The heading names in Column A of the List sheet were made into a named range ("H_Names") and were placed into a table (Table1) which can be added to when or if need be.
As you can see above, the named range has been placed into an array.

I believe that this method may just suit you also. I've attached a sample of the other Poster's file with dummy data. Click on the "RUN" button to see how it works. Play with it as you like by adding/deleting columns, adding/deleting heading names in the List sheet.

I hope that this helps.

Cheerio,
vcoolio.

 

[Gerry:::::)]

re-
' I really need a way to find the specific columns rather than the below if statement which just checks if the column contains the specific value I wish to manipulate.

Thanks for the Code, but its almost the opposite VBA to what i am after, reasons being that I never know what colums names are that IT Dept's DB's might add to the extract, So what I am looking for is to get the (known) existing columns and delete all the rest including any they add, so as I said in the OP, I need to find the columns i am interested in and move these to another sheet (on there own) to manipulate. That way they can add as many as they like and my vba will still run perfectly

 

[Marc L]

Hi !​

​

You even no need any VBA code (first to try) just using Excel basics : an easy advanced filter …​

​

 

[Gerry:::::)]

needs to be done via VBA..most users are clueless...thanks though

 

[Marc L]

​

As I wrote « (first to try) », nothing easier than an advanced filter even inside a VBA code as you can see​

within the threads samples from this VBA forum section or even in the course / lesson of this Chandoo site !​

​

 

[Gerry:::::)]

Thanks but so sorry, advanced filter is not what I am looking for, a sfar as i know it can only copy specified areas to a new sheet, So what I realy need is ...' I really need a way to find the specific columns rather than the below if statement which just checks if the column contains the specific value I wish to manipulate.

If Range("O2").Value = "VIDEO" And Range("P2").Value = "EVENT" Then

    Application.CutCopyMode = False
    Columns("O:P").Select
    Selection.Delete Shift:=xlToLeft

end if

 

[Gerry:::::)]

thanks everyone, so sorry you were not able to help me.. I'll now try another forum more up on solving this issue and specifically geared to what I am after. I let you guys know the solution when I know too..thanks to all

 

[Gerry:::::)]

Hi Everyone,
Its probably not the most efficient code, and likely could be tightened up to run quicker if I new how, but I finally found a simple piece of VBA to manipulate my data this when tweaked a little will solve my issue, so I've posted this below in the hopes it may help anyone else with a similar issues to mine.

Sub Find_header()

Dim col As String, cfind As Range

    Worksheets(1).Activate
    
    col = "DATE_FROM"

    Set ColumnHeaderFound = Cells.Find(what:=col, lookat:=xlWhole)


        ColumnHeaderFound.End(xlUp).Select
        Range(Selection, Selection.End(xlDown)).Copy
        
        ActiveSheet.Range("C1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
                 Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        ColumnHeaderFound.End(xlUp).Select
        Range(Selection, Selection.End(xlDown)).Delete ' Deletes the entire orginal column
    
            

End Sub

 

[Hui]

Gerry

Can you please attach a sample file so we can see what you are trying to achieve