Please show me how to automatically add a blank column before each table

[vijnanamatrata]

Hi everybody,


I have a task which needs automation with use of macro and I have written code to do it; however, my code could not do the task successfully. Therefore, I need your help now.


Please download my sample file, including 3 sheets: “start_1”, “start_2” and “result”. “start_1” and “start_2” are identical. I need to format tables in these two sheets “start” so that they look like exactly the “result” sheet.


http://www.mediafire.com/download/n8dwli8v55lw9gl/add_a_blank_column_before_each_table.xls


In detail, here are tasks which need automating:

- Add 1 blank row at top (I have done it)

- Freeze title rows (I have done it)

- This is what I could not do: Add 1 blank column before each table. The added column must have no fill color and no border at all. (Each table here is identified by merged cells in the top title row)

- Also please show me how to determine the last column with data and the last row with data so that the blank outer space is hidden.


Thank you very much for your help.

Below is my code (in ThisWorkbook module):

[pre]

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim bScrUpdate As Boolean
Dim ws As Worksheet
Dim rng As Range

Application.EnableCancelKey = xlDisabled 'disable ESC key
bScrUpdate = Application.ScreenUpdating
If bScrUpdate = True Then Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "result" Then
ws.Select
Rows("1:1").Insert Shift:=xlDown

Range("A4").Select
ActiveWindow.FreezePanes = True

For Each rng In Rows("2:2").Cells
If rng.MergeCells Then
rng.MergeArea.Cells(1, 1).Select
Selection.Offset(-1, 1).EntireColumn.Insert Shift:=xlToRight,CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next rng
End If
Next ws

Application.DisplayAlerts = True
If Not Application.ScreenUpdating = bScrUpdate Then Application.ScreenUpdating = bScrUpdate
Application.EnableCancelKey = xlInterrupt 'enable ESC key
End Sub

[/pre]

 

[Hui]

Vijnanamatrata


Firstly, Welcome to the Chandoo.org forums


This will insert the columns for you

You will need to insert into your code as appropriate

[pre]

Sub Insert_Columns()
Dim c As Range
Set c = Worksheets("Start_1").Range("IV1").End(xlToLeft)
Do While c.Address <> "$A$1"
If c.MergeCells Then
Columns(c.Column).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

With Columns(c.Column - 1).Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

With Columns(c.Column - 1)
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End If
Set c = c.End(xlToLeft)
Loop

End Sub

[/pre]

 

[Sam Mathai Chacko]

Hui, if you are changing the line style for all the possible eight border lines, then you might as well use


Columns(c.Column - 1).Borders.LineStyle = xlNone

 

[Hui]

I didn't do that as I don't want to clear the side borders.

 

[Sam Mathai Chacko]

For a moment I thought you used all of the 8 borderline indices. Apparently, you've only used 6. But I'm curious to know what difference does it make to use

[pre]

.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone

[/pre]

twice in the code, which lead me to think that you've used all 8?