CODEWITHSUNDEEP
excelvba
NCRI MTL
NCRI MTL

Reputation: 23

multiple dims lastrow vba

Im not too familiar with VBA, so the code below is mostly just copy and paste and research, just cant couldnt find a way to use Dim multiple times to be able to use it for different columns. Is there something I could add in front of "lastrow" to make it a different without getting the "compile error: Duplication declaration in current scope".

   Dim lastrow As Long
        lastrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
   Dim pointer As Integer
        pointer = 1
   Do Until pointer > lastrow - 1
        Range("D1").Select
        Sheet1.Range("D" & pointer).NumberFormat = "mm/dd/yyyy"
        pointer = pointer + 1
   Loop

Upvotes: 0

Views: 691

Answers (2)

John Coleman
John Coleman

Reputation: 52008

You can't dim a variable multiple times in one function or sub (hence the syntax error). There are some programming languages such as Java which has a notion of block-level scope in which such redeclarations are possible. VBA is not one of those languages.

Perhaps this is an XY problem and what you really want to do is to reinitialize the variable in each pass through an outer loop. That is nonproblematic, just lastrow = 0 whenever you are tempted to redeclare it.

That said, for this particular problem follow the recommendations of braX. In VBA, it is often possible to deal with ranges as a whole rather than looping cell by cell.

Upvotes: 0

braX
braX

Reputation: 11755

You can remove the line with .Select in it, as it is not needed, and you can set the .NumberFormat of multiple cells within the loop if you want.

But there is an easier way, you can set the .NumberFormat property of an entire column with one line of code and no loop:

WorkSheets("Sheet1").Range("D:D").NumberFormat = "mm/dd/yyyy"

And for that matter, you can do multiple columns all at once too...

WorkSheets("Sheet1").Range("D:E,G:G,I:J").NumberFormat = "mm/dd/yyyy"

Upvotes: 2

Related Questions