improving this piece of code

[Hlatigo]

I am trying to find a way where I can convert this piece of code into one where I can combine the ranges. Although I am ok with coding, i feel its time I start to learn how to write more efficient code. any help would be great.

 'BenchMark
    lastrow = Range("D4000").End(xlUp).Row
    Range("D6:D" & lastrow).Select
      With Selection
         .NumberFormat = "general"
      End With
        
    lastrow = Range("E4000").End(xlUp).Row
    Range("E6:E" & lastrow).Select
      With Selection
        .NumberFormat = "m/d/yyyy"
      End With
    
    'minorcurve1
    lastrow = Range("L4000").End(xlUp).Row
    Range("L6:L" & lastrow).Select
      With Selection
         .NumberFormat = "general"
      End With
        
    lastrow = Range("M4000").End(xlUp).Row
    Range("M6:M" & lastrow).Select
      With Selection
        .NumberFormat = "m/d/yyyy"
      End With

 

[njimack]

lastrow = Range("D4000").End(xlUp).Row
Range("D6:M" & lastrow).NumberFormat = "general"

 

[Norie]

Well for a start you can lose the Select and the With.

 'BenchMark
    lastrow = Range("D4000").End(xlUp).Row
    Range("D6:D" & lastrow).NumberFormat = "general"
    
    lastrow = Range("E4000").End(xlUp).Row
    Range("E6:E" & lastrow).NumberFormat = "m/d/yyyy"
      
    'minorcurve1
    lastrow = Range("L4000").End(xlUp).Row
    Range("L6:L" & lastrow).NumberFormat = "general"
        
    lastrow = Range("M4000").End(xlUp).Row
    Range("M6:M" & lastrow).umberFormat = "m/d/yyyy"

And I also wonder if you actually need to find the last row 4 times.

 

[Jonmo1]

Try this

lastrow = Activesheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
Range("D6:D" & lastrow & ",L6:L" & lastrow).NumberFormat = "general"
Range("E6:E" & lastrow & ",M6:M" & lastrow).NumberFormat = "m/d/yyyy"

Hope that helps...

 

[Hlatigo]

Hi neil...thanks but wouldnt that format everything between those columns?


Hi Norie!
I thought about that as well, the first last row (benchmark) would predicate where the others will have their last row even if data continues past that row.

So is it possible to set all based on the benchmark.

the cose I show is only half. I have this duplicating 4 times right now and I assume people will request for more (minor curves), which would force me to duplicate over and over.

 

[Hlatigo]

thanks jonmo1

worked great. I just expanded it to include the other curves

lastrow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
    Range("D6:D" & lastrow & ",L6:L" & lastrow & ",U6:U" & lastrow & ",AD6:AD" & lastrow).NumberFormat = "general"
    Range("E6:E" & lastrow & ",M6:M" & lastrow & ",V6:V" & lastrow & ",AE6:AE" & lastrow).NumberFormat = "m/d/yyyy"