Multiply columns to 1

[LeanneBG]

Hi Experts!

I have been trying to figure out how to multiply 3 columns (column C, F and I) by 1 and paste as values -- up until last row but excluding the header since the header is text and will generate an error if i do this.

I tried recording the process of how i do it but i'm getting a numerous line items of code. I believe there's a shorter way to do it. Appreciate if anyone can suggest a shorter way. Thanks in advanceE

 

[footoo]

Why do you want to multiply by 1 ?

 

[LeanneBG]

Why do you want to multiply by 1 ?

To change the format of the date and time under those columns. I have a formula that cannot read those unless i multiply the data to 1..

 

[Elaszat]

Hi Leanne,

relatively easy, you don't even need VBA code.

You write 1 in any cell, copy that cell. Then you select the data you want to multiply by 1. Rightclick, then Paste Special. From there, select Multiply in the Operation section and click OK.

Regards,
Elaszat

 

[LeanneBG]

Hi Leanne,

relatively easy, you don't even need VBA code.

You write 1 in any cell, copy that cell. Then you select the data you want to multiply by 1. Rightclick, then Paste Special. From there, select Multiply in the Operation section and click OK.

Regards,
Elaszat

Hi Elaszat
Thanks for the tip. As much as possible i wanted to lessen the manual intervention by utilizing vba. Is there a way to put the above into vba codes? I already tried recording but it creates multiple line items of code

 

[Elaszat]

There is. I'll come back to that tomorrow, cause I'm running out of time here.

 

[LeanneBG]

There is. I'll come back to that tomorrow, cause I'm running out of time here.

Thank you!

 

[Fluff]

How about

Sub LeanneBG()
    With Range("O2")
        .Value = 1
        .Copy
        Range("A1").CurrentRegion.PasteSpecial xlPasteAll, xlMultiply
        .Clear
    End With
End Sub

Change O2 to any cell that is outside your data range

 

[Elaszat]

Hi Leanne,
sorry for leaving so abruptly yesterday.

While the code by @Fluff does what I described, I thought of another apporach:

Private Sub MultiplayByOne()
Dim b, i As Range
    b = InputBox("Please enter designated area.", "Multiply by 1")
    For Each i In ActiveSheet.Range(b)
    i = i * 1
    Next
    MsgBox "Area hast been multiplied by 1."
End Sub

When you start this sub, you are met with an Input Box that is asking for the area you want to multiply, then it does exactly that.
Note, that your area has to be without gaps, so you can't multiply columns C and F at once, but you could multiply columns C to F (including D and E) at once.

Regards,
Elaszat

 

[Peter_SSs]

To change the format of the date and time under those columns.

If it is that your values are Date/Time stored as text, then you might also try this method. This assumes that your dates are in D/M/Y format. If they would be M/D/Y format then change the FieldInfo array to Array(1, 3)

Sub Convert_Values()
  Dim Col As Variant
 
  For Each Col In Split("C F I")  '<-List your columns here with spaces
    Columns(Col).TextToColumns DataType:=xlDelimited, Space:=False, Other:=False, FieldInfo:=Array(1, 4)
  Next Col
End Sub