VBA - Border on top only

[Grek]

Hello,

I'm trying to modify the existing code of an application to reflect what I want to do.

Currently this part of the code add a border on the top and bottom of a cell

Sub ColorizeLastRow(row%, sht As Excel.Worksheet)
  Call sht.Range("A" & row & ":BE" & row).BorderAround(xlDash, xlMedium, xlColorIndexAutomatic)
End Sub

However I would like a border on the top only. I have tried different things without success....

Any idea?

Thanks,

 

[chergh]

Sub ColorizeLastRow(row%, sht As Excel.Worksheet)
  with sht.Range("A" & row & ":BE" & row).Border(xltop)

       .linestyle = xlDash
       .weight = xlMedium
   
   end with
End Sub

 

[Grek]

Thanks, unfortunately it doesn't seem to be working :-/
I get this error message: "Object doesn't support this property or method"

 

[ghudson]

Open the immediate window in Excel VBA editor and type ?xltop to get the numerical value for that option. Do the same for any XL options like xlDash and xlMedium.

?xltop 
-4160 

?xlDash 
-4115 

?xlMedium
-4138

 

[chergh]

Open the immediate window in Excel VBA editor and type ?xltop to get the numerical value for that option. Do the same for any XL options like xlDash and xlMedium.

It's not that, as he used excel.worksheet he is using early binding so can use excel constants.

All thats wrong is a typo in my code, I missed the "s" of borders. It should be:

Sub ColorizeLastRow(row%, sht As Excel.Worksheet)
  with sht.Range("A" & row & ":BE" & row).Borders(xltop)

       .linestyle = xlDash
       .weight = xlMedium
   
   end with
End Sub

 

[Grek]

Thanks, it works perfectly now
Thanks again

 

[Grek]

Quick question though:
It seems to be much slower to generate a report using cherg's code than the old code
Is there a specific reason to that?