VBAl only works for a small amount time before failing

gint32

Board Regular
Joined
Oct 8, 2014
Messages
120
Hi Everyone. Having an issue with my vba below. Our developers every now and then (as per normal) update our system (crystal reports) by adding or deleting extra columns, I export these reports to a CSV Excel file then run my macro on the csv to delete and manipulate the columns and data within.

I trying to figure a way of amending my macro to prevent them from failing every time they insert a few extra columns, FYI the raw csv columns have headers which I eventually rename to something more meaningful and relevant.

Its been suggested that I try using advanced filtering with vba, but I am really not up on this method to take it any further so would need help with this route if this is a valid option.

Otherwise, here's a simple sample of what I have so far.

Code: <code> ' I really need the VBA to "FIND" the column header rather than test a specific column header value and data within.


If Range("O1").Value = "Some_HeaderInfo" And Range("O2").Value = "DATA_001" <code>And Range("O3").Value = "DATA_003"</code> Then

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

end if</code>
 
Last edited:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

gint32

Board Regular
Joined
Oct 8, 2014
Messages
120
Hi Everyone, I have had a search and I came across the following vba to search, its pretty close to suit my needs, Although, I just need a little help with amending the vba to get last row instead of selecting the entire column..Can anyone help me with this

Thanks
Sub FindAddressColumn()

' From https://www.extendoffice.com/documents/excel/4879-excel-select-column-by-header-name.html

Dim xRg As Range
Dim xRgUni As Range
Dim xFirstAddress As String
Dim xStr As String
Dim LastRow as Long

On Error Resume Next
xStr = "Name"
Set xRg = Range("A1:P1").Find(xStr, , xlValues, xlWhole, , , True)
If Not xRg Is Nothing Then
xFirstAddress = xRg.Address
''''Do
Set xRg = Range("A1:P1").FindNext(xRg)
If xRgUni Is Nothing Then
Set xRgUni = xRg
Else
Set xRgUni = Application.Union(xRgUni, xRg)
End If

'''Loop While (Not xRg Is Nothing) And (xRg.Address <> xFirstAddress)

End If

xRgUni.EntireColumn.Select ' need to select only down to lastrow used

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,436
Messages
5,528,749
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top