VBA - Applying border around the areas with value text

0 votes

The current code :

Dim border As Range
Dim brng As Range

Set border = ThisWorkbook.ActiveSheet.UsedRange

For Each brng In border
brng.BorderAround _
    LineStyle:=xlContinuous, _
    Weight:=xlThin
End If
Next brng

The screenshot on the left is what I currently get, and the screenshot on the right is what I am trying to achieve:

enter image description here

Mar 31, 2023 in Others by narikkadan
• 63,700 points
382 views

1 answer to this question.

0 votes

Try this:

Sub BorderArroudAreas()
   Dim sh As Worksheet, lastR As Long, rng As Range, rngBord As Range, arrBord, El, A As Range
   
   Set sh = ActiveSheet
   lastR = sh.Range("B" & sh.rows.count).End(xlUp).row 'last row on B:B
   
   Set rng = sh.Range("B1:B" & lastR).SpecialCells(xlCellTypeConstants) 'the B:B discontinuous range (empty rows is a delimiter for the range areas)
   
   'obtain a range having the same areas in terms of rows, but all used range columns:
   Set rngBord = Intersect(rng.EntireRow, sh.UsedRange.EntireColumn)
   
   'create an array with numbers from 7 to 12 (the borders type constants...)
   arrBord = Application.Evaluate("Row(7:12)") 'used to place cells borders
   
   For Each A In rngBord.Areas 'iterate between the range areas
        For Each El In arrBord 'place borders on each area cells:
             With A.Borders(El)
                   .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = 0
             End With
         Next El
   Next A
End Sub
answered Mar 31, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,700 points
627 views
0 votes
1 answer

How to retrieve the value of a text field in Flutter App?

Hi@akhtar, In your TextField, you can call one ...READ MORE

answered Sep 3, 2020 in Others by MD
• 95,440 points
1,562 views
0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,700 points
814 views
0 votes
1 answer

Text with a dash in the cell but not in the formula bar (Excel)

What you have got here is called ...READ MORE

answered Nov 13, 2022 in Others by narikkadan
• 63,700 points
1,835 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
952 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,282 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
576 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
806 views
0 votes
1 answer
0 votes
1 answer

Excel formula to get certain cell if the value is between 2 numbers

So, first with vlookup(): A formula so you ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
776 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP