can pls help on this Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Range("A1") "" Then Range("A1").Select Selection.AutoFill _ Destination:=Range("A1:A5"), Type:=xlFillDefault 'Range("H1").Value = "=IF(AND(F5="",G5="",H5="")),"",(I4+F5-G5-H5)" End If 'End With End Sub
In folder X, I have 2 excel files from column A to column H. Now I want to delete the unnecessary columns B: D, F, H in both excel files of the folder. How to write VBA code, so that when running the code, a message box will appear looking for the X directory address (or you can enter the X directory path directly into the code)
Sub deletecolumn() Dim MyFolder As String Dim MyFile As String Application.DisplayAlerts = False Application.ScreenUpdating = False MyFolder = "E:\Exp" MyFile = Dir(MyFolder & "\*.xlsx") Do While MyFile "" Set OpenBook = Application.Workbooks.Open(MyFolder & "\" & MyFile) OpenBook.ActiveSheet.Range("B:D,F:F,H:H").Delete OpenBook.ActiveSheet.Range("A1").Select MyFile = Dir OpenBook.Close SaveChanges:=True Loop Application.ScreenUpdating = True End Sub
