200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello !
I have this code , but it takes more time because it will implement for the whole column . what I want just include the cells contain numbers in column C and ignore empty cells to the code become fast .
Sub add_currency()
With sheet2
.Cells(1, 2).Resize(.Rows.Count, 2).NumberFormat = Array("#,0.00", "[$TRY ]#,##0.00")
End With
End Sub
any idea guys ?
Tubrak
If you use .Rows.Count alone, Excel will resize the range to the largest possible rows in a column, i.e. 1,048,576 rows (so look at thousands of rows with no data and waste lots of time).
Better to use a simple loop to the last cell with data and an IF statement to format only the cells with data. Try this revised code (with comments):
Sub add_currencyFaster()
With Sheet2
'Loop until last used cell in column 2 (B)
For n = 1 To .Cells(Rows.Count, 2).End(xlUp).Row
'Set format if there's something in the cell
If Not IsEmpty(.Cells(n, 2)) Then .Cells(n, 2).NumberFormat = "[$TRY ]#,##0"
Next n
End With
End Sub
Hope this works for you. (If you have thousands of rows and need more speed, you could turn off ScreenUpdating within the With scope but I doubt you will).