Modify the Macro to Autofill hidden Column

[xcruc1at3r]

Column A is Hidden and i need to Modify it When i delete rows and add new information based rows... so due to which i have to unhide the Row A in all sheets and AutoFill them and again Hide the Column A....


Sub autofill()

Sheets(Array("USM Enroute", "MNR Avaliable", "COMMERCIAL", "DMG", "CLAIM", _

"Un-traceble")).Select

Sheets("USM Enroute").Activate

Columns("A:C").Select

Range("C1").Activate

Selection.EntireColumn.Hidden = False

Range("A2").Select

Selection.autofill Destination:=Range("A2:A851")

Range("A2:A851").Select

Columns("A:A").Select

Selection.EntireColumn.Hidden = True

Sheets("USM Enroute").Select

Sheets("Database").Select

Range("A2:A3").Select

Selection.autofill Destination:=Range("A2:A851")

Range("A2:A851").Select

Range("J2").Select

Selection.autofill Destination:=Range("J2:J851")

Range("J2:J851").Select

Range("A1:I851").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.Weight = xlMedium

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.Weight = xlMedium

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlMedium

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.Weight = xlMedium

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

Range("B3").Select

End Sub

 

[Luke M]

You don't need to unhide the column to modify the data in it. Also, you didn't mention anything about formatting cells, so I left out that big chunk of code. Here's what I ahd left:

[pre]

Sub AutoFill()
Dim LastRow As Integer
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
With ws
'I'm assuming you want to AutoFill compared to col B. If not, change the 2
'in the next line as appropaite (A=1, B=2, etc)
LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
.Range("A2").AutoFill .Range("A2", .Cells(LastRow, 1))
End With
Next ws
Application.ScreeUpdating = True
End Sub

[/pre]

 

[xcruc1at3r]

luke what about In Only Sheet.Database!J:2:J851? i need to autofill that column as well because when we insert rows so the previous data is deleted as ROW delete... so i need to fill the A ranage till 851 In all Sheets and J till 851 In only Sheet.Database!

 

[Luke M]

We can just throw in an If statement to look for that...

[pre]

Sub AutoFill()
Dim LastRow As Integer
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
With ws
'I'm assuming you want to AutoFill compared to col B. If not, change the 2
'in the next line as appropaite (A=1, B=2, etc)
LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
.Range("A2").AutoFill .Range("A2", .Cells(LastRow, 1))
End With

If ws.Name = "Database" Then
With ws
'I'm assuming you want to AutoFill compared to col B. If not, change the 2
'in the next line as appropaite (A=1, B=2, etc)
LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
.Range("J2").AutoFill .Range("J2", .Cells(LastRow, 1))
End With
End If
Next ws
Application.ScreeUpdating = True
End Sub

[/pre]

 

[xcruc1at3r]

LUKE SHOULD I SEND U THE File? so u can get the clear picture !!...

the File is really BIG in size 5.4MB cause it has alots of calculations and macros

 

[xcruc1at3r]

I JUST REMOVED THE HIDE AND UNHIDDEN CODE PROBLAMO SOLVED