I dont exactly understand the concept or all the variations of DIM, but I know that I am declaring it as a variable, something unique. But half the time it seems like I dont need to use DIM at all
For example, instead of
Dim LastRow
LastRow = Range("F1").End(xlDown).Row
Sometimes I can just skip the Dim bit and just use
LastRow = Range("F1").End(xlDown).Row
Continuing the example of never using Dim the following works just fine:
For Each cell In Range("G2:G" & LastRow)
If cell.Value = "0" Then cell.Clear
Next
Why does this work without ever having to do the Dim part? Does Dim make the variable available in other macros? Is Dim only good for that execution of the code? Same question for not using Dim.
Another example of not using Dim, later on in my code same module I am sorting some data, and now I am getting an error, an I wonder if its because I didnt use Dim?
SortStart = Range("F1")
SortEnd = Range("H" & LastRow)
CriteriaStart = Range("G2") 'Change this column reference to change sort Criteria
CriteriaEnd = Range("G" & LastRow)
ActiveWorkbook.Worksheets("Scratch").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scratch").Sort.SortFields.Add Key:=Range(CriteriaStart & ":" & CriteriaEnd), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Scratch").Sort
.SetRange Range(SortStart & ":" & SortEnd)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For example, instead of
Dim LastRow
LastRow = Range("F1").End(xlDown).Row
Sometimes I can just skip the Dim bit and just use
LastRow = Range("F1").End(xlDown).Row
Continuing the example of never using Dim the following works just fine:
For Each cell In Range("G2:G" & LastRow)
If cell.Value = "0" Then cell.Clear
Next
Why does this work without ever having to do the Dim part? Does Dim make the variable available in other macros? Is Dim only good for that execution of the code? Same question for not using Dim.
Another example of not using Dim, later on in my code same module I am sorting some data, and now I am getting an error, an I wonder if its because I didnt use Dim?
SortStart = Range("F1")
SortEnd = Range("H" & LastRow)
CriteriaStart = Range("G2") 'Change this column reference to change sort Criteria
CriteriaEnd = Range("G" & LastRow)
ActiveWorkbook.Worksheets("Scratch").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scratch").Sort.SortFields.Add Key:=Range(CriteriaStart & ":" & CriteriaEnd), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Scratch").Sort
.SetRange Range(SortStart & ":" & SortEnd)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With