Help with VBA

saroo970

New Member
Joined
Oct 4, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
I have 100 excel sheets all of them are identical with seven columns (Date, Open, High, Low, Close, Adj Close, Volume) what I need is to

1- delete all the columns in all 100 excel sheets inside the folder but keep only two columns (“Date”, and the “Close”)
2- I need to format all the 100 sheets the first column “Date” columns to be (dd-mmm-yyyy) and the “close” columns to be in this format 0.00000 which means the number should be like this 5.12345.

Thanks,
Sam
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
It would be easier to write a script if you were to say what columns:
Like column 1 and 2 and 5 and 6

And you said:
all 100 excel sheets inside the folder
Do you mean inside the Workbook.
And you said
but keep only two columns (“Date”, and the “Close”)
What columns would this be?
Say columns 5 and 6 or something like that.

So the script would be written to delete columns 5 and 6 on each sheet in the workbook.
 

saroo970

New Member
Joined
Oct 4, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
the folder contains 100 excel sheets (separate sheets) all of them are identical and each sheet has 7 columns (as shown in the attached screenshot) which are (Date, Open, High, Low, Close, Adj Close, Volume) I just need to keep two columns only (Date and Close) the rest I don't need them. the second thing is formating these columns the date should be in the format of dd-mmm-yyyy and the close should 0.00000

so the script needs to delete the unwanted columns and keep on two for the all the 100 excel sheets that inside the folder

hope I could explain it well.

Thanks
Sam
 

Attachments

  • Capture.PNG
    Capture.PNG
    29.6 KB · Views: 10

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
You said:
the folder contains 100 excel sheets
Folders do not contain sheets

Folders contain Workbooks
Workbooks contain sheets.
 

saroo970

New Member
Joined
Oct 4, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

workbooks, sorry
 

saroo970

New Member
Joined
Oct 4, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
the columns to be deleted are (B, C, D, F, and G) only A, and E should remain the second part the format A columns (dd-mmm-yyyy) and E as numbers to be formated (0.00000)
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You said:
The second thing is formating these columns the date should be in the format of dd-mmm-yyyy and the close should 0.00000
the columns to be deleted are (B, C, D, F, and G) only A, and E should remain the second part the format A columns (dd-mmm-yyyy) and E as numbers to be formated (0.00000)
And are you saying these columns are not already formatted the way you want?
If we don't delete them then why do we need to format them?

Try this:
VBA Code:
Sub Delete_Columns()
'Modified  10/4/2021  11:37:51 AM  EDT
Application.ScreenUpdating = False
Dim Del As Variant
Del = Array(2, 3, 4, 6, 7)
Dim i As Long
Dim b As Long

For b = 1 To Sheets.Count
    With Sheets(b)
        For i = UBound(Del) To 0 Step -1
            .Columns(Del(i)).Delete
        Next
    End With
Next
Application.ScreenUpdating = True
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
@My Aswer Is This that code is deleting the columns in every sheet in one workbook, not the 100 workbooks in the folder. ;)
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
In case your wondering my script deletes columns in all the sheets in your workbook.
If you only wanted 100 sheets to be included you would have to say what 100 sheets.
 

Forum statistics

Threads
1,148,177
Messages
5,745,194
Members
423,932
Latest member
pablo2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top