2

Starting From B5, C5, D5, .... I have an excel file which looks like below:

enter image description here

Please be advised that I have some titles and heading text on first 3 rows so I need to have Macro to set type of each columns from row 5 to end based on the title of the rows( titles are just for mentioning the required type) can you please let me know how I can do this in Excel VBA?

Thanks

Community
  • 1
  • 1
Behseini
  • 6,066
  • 23
  • 78
  • 125

1 Answers1

1

Is this what you are trying? I am showing an example for Column B. Do it for the rest.

Logic:

  1. Find Last Row in a Column. See THIS
  2. Construct your range
  3. Format the range as required.

Code:

Private Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, Header As Long

    Header = 5 '<~~ Start row for formatting

    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row

        With .Range("B" & Header & ":B" & LastRow)
            '
            '~~> Change format here
            '
             '~~> Number with 5 decimal places.
            .NumberFormat = "0.00000"
        End With
    End With
End Sub

FOLLOWUP FROM COMMENTS

Thanks but this just formatting the B5 cell, can you please let me know how i can do the rest of rows from 5 to like 1000 – Behseini 11 secs ago

Oh so if there are no values after row 5 and you want to hardcode the last row then use this code

Private Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, Header As Long

    Header = 5     '<~~ Start row for formatting
    LastRow = 1000 '<~~ Last Row

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        With .Range("B" & Header & ":B" & LastRow)
            '
            '~~> Change format here
            '
            '~~> Number with 5 decimal places.
            .NumberFormat = "0.00000"
        End With
    End With
End Sub
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks Siddharth but how to change the format? i mean how can I specfiy the column to be Number with 5 Decimal place for example? – Behseini Dec 17 '13 at 04:22
  • @Behseini: I have updated the code. Refresh the page to see the updated reply. – Siddharth Rout Dec 17 '13 at 04:28
  • Thanks but this just formatting the B5 cell, can you please let me know how i can do the rest of rows from 5 to like 1000 – Behseini Dec 17 '13 at 04:33