Hi,
I have code, when I reduce number of columns, I need only columns matching header name. The problem is that in the data source (SAP report) are twice.
So I count the number of occurrences and for one case I am able to delete the second column as this occurrence is always on second column but in the second case there is problem that in one report it is in column G and in month earlier it is in column F (not sure how this happened). So I need to determine the position of second column and this delete this column
The headers after first part looks like this (the problematic part is in bold)
Debits indicator Description Determination characteristic result line Description G/L Account Amount Determination characteristic result line
or like this
Debits indicator Description Determination characteristic result line Description G/L Account Determination characteristic result line Amount
Is there any way to do this? to determine the address of second occurrence of the case and delete the whole column?
Thank you very much.
I have code, when I reduce number of columns, I need only columns matching header name. The problem is that in the data source (SAP report) are twice.
So I count the number of occurrences and for one case I am able to delete the second column as this occurrence is always on second column but in the second case there is problem that in one report it is in column G and in month earlier it is in column F (not sure how this happened). So I need to determine the position of second column and this delete this column
The headers after first part looks like this (the problematic part is in bold)
Debits indicator Description Determination characteristic result line Description G/L Account Amount Determination characteristic result line
or like this
Debits indicator Description Determination characteristic result line Description G/L Account Determination characteristic result line Amount
VBA Code:
lcol = Cells(1, columns.Count).End(xlToLeft).Column
'removes all columns not matching the case, starting from end
For delcol = lcol To 1 Step -1
Select Case Cells(1, delcol)
Case "Debits indicator", "Determination characteristic result line", "Description", "G/L Account", "Amount"
'will do nothing if the value is matched
Case Else
columns(delcol).Delete
End Select
Next
CountDesc = Application.CountIf(Range("A1:Z1"), "Description")
CountDete = Application.CountIf(Range("A1:Z1"), "Determination characteristic result line")
If CountDesc > 1 Then
Range("B:B").Select
Selection.Delete Shift:=xlToLeft 'delete first Description column in column B
End If
If CountDete > 1 Then
'TBD - there should be the check for Deterministic characteristic result line header
End If
Is there any way to do this? to determine the address of second occurrence of the case and delete the whole column?
Thank you very much.