Kostas1977
New Member
- Joined
- Nov 27, 2021
- Messages
- 4
- Office Version
- 365
- 2019
- Platform
- Windows
I am loading a CSV file into a table with rows from the cell A17 until AG110.. The CSV is not the same every time, so I delete the empty rows of the table.
My problem are the columns.. Since the CSV is different next time I might have more or less columns.
So I need a dynamic table that "reads" the headers of the table from the first line of the CSV file.. and of course "changes" the settings for Autofit and HorizontalAlignment.
Sorry that I have posted all my code but I think it is necessary for understanding of what I am trying to do.
My code is as folllows:
My problem are the columns.. Since the CSV is different next time I might have more or less columns.
So I need a dynamic table that "reads" the headers of the table from the first line of the CSV file.. and of course "changes" the settings for Autofit and HorizontalAlignment.
Sorry that I have posted all my code but I think it is necessary for understanding of what I am trying to do.
My code is as folllows:
VBA Code:
Dim wbCSV As Workbook, n As Long
Dim ws As Worksheet
Set ws = ActiveSheet
Dim currentSheet As Worksheet
Set currentSheet = ActiveSheet
Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
With sheet
With .Cells.Rows
.WrapText = True
.VerticalAlignment = xlCenter
.EntireRow.AutoFit
End With '.Cells.Rows
.Columns.EntireColumn.AutoFit
End With 'sheet
Next sheet
currentSheet.Activate
Dim rng As Range
Set rng = Range("A17:T110")
Set ws = ActiveSheet
Set wbCSV = Workbooks.Open(folder & fileName, False, True)
With wbCSV.Sheets(1)
n = .UsedRange.Rows.Count
.UsedRange.Copy ws.Range("A17")
End With
With rng.Borders
.LineStyle = xlContinuous
.Color = vbBlack
.Weight = xlThin
End With
wbCSV.Close False
ws.Range("A17:AG17").Interior.Color = RGB(147, 175, 186)
ws.Range("A17:AG17").Font.Bold = True
ws.Range("A17:AG17").Font.Size = 12
ws.Columns("A:AG").HorizontalAlignment = xlCenter
ws.Columns("A:AG").AutoFit
ws.Cells.WrapText = True
ws.Columns("C").NumberFormat = "# €_)"
ws.Columns("C").HorizontalAlignment = xlLeft
ws.Rows("7").Resize(n).AutoFit
ws.Columns.AutoFit
'check for filter, turn on if none exists
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A17").AutoFilter
End If
Dim EntireRow As Range
Set rng = Range("A17:AG110")
If Not (rng Is Nothing) Then
Application.ScreenUpdating = False
For I = rng.Rows.Count To 1 Step -1
Set EntireRow = rng.Cells(I, 1).EntireRow
If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End If
rng.BorderAround _
ColorIndex:=1, Weight:=xlThick