Add New Column

[amit186]

Hi All,

I have a requirement where I have to format a given spreadsheet which has multiple columns(almost 20 ) and 400-500 rows to have 3 columns only of which 2 columns come from the base spreadsheet and the 3rd column is Date column for which I have to prompt user.

I have the macro for to delete the all the other columns and have the spreadsheet with 2 columns done but I am having difficulty to add the 3rd column.

Any ideas ?

 

[JackDanIce]

Hi, welcome to the board.

Have a look at this link to create a pop-up calendar for the user to select a date from:
http://www.yogeshguptaonline.com/search/label/Excel%20Pop%20up%20Calendar

Once this populates a cell in your third column (let's assume it populates into C1), you can then fill in the rest of your third column as:

Dim i as Long
i = Range("A" & Rows.Count).End(xlUp).Row)
With Range("C2:C" & i)
 .Formula = "=$C$1"
 .Value = .Value
End With

 

[amit186]

Thanks so much for the reply. I was finally able to resolve it as follows:

I prompt the user to enter the date in=>

cDate= InputBox("Enter the Date in (DD/MM/YY) for which you want to insert the data E.g. 09/12/10")

Then using the code as above

Range("C1").Value = "Date"
Range("C1").Font.FontStyle = "Bold"

i = Range("A" & Rows.Count).End(xlUp).Row
With Range("C2:C" & i)
.Value = cDate
End With

Thanks again !