Dynamically Adding a Border Based on Rows Left in Each Section (Excel VBA)

ahmadazhan

New Member
Joined
Aug 24, 2015
Messages
6
I created a macro that is pulling in data from another sheet and pastes it into a new spreadsheet. It pulls in 3 main sections of data. It pastes it all, does a VLOOKUP on the corresponding columns it needs information based on what it pulled in. After it's done that, I have a "Remove" macro that removes the empty rows so they spacing is cropped down. I need to now add a border around those three different sections. The issue is, I have no experience with macros/VBA so I am trying my best to get this together but if I create a macro to put a border around what's left after it's removed empty spaces, it is not dynamic. So thus, if I did it for another spreadsheet that had more rows in each section, it wouldn't put the border around the right areas. I will need it to dynamically look through each of my three sections and see where the last row with information is and then put a border around that.

So here is a quick example: Say I have three empty sections currently before I've pulled in my data. Section one ranges from cells B1:K50. Section two ranges from B52:K100. Section three ranges from B105:K150. Once I hit my "pull in data" macro, it will auto populate those cells but lets say all those extra rows I have for each section wasn't used, I will have my "Remove" macro remove those extra. So lets say after it's removed those extra rows, it'll shift up where my sections 2 and sections 3 were. Then the final step will be to have it search to see where the sections begin and end and add a border around each of those sections. I can paste my code and we can go from there since I figured that'd be the best since I don't actually fully understand the syntax.

I would really appreciate any help!
:)
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Do you have the code your working with?
 
Upvote 0
Here you go, this macro code should do as you need it to, i have added comments to help you understand it a bit better and mould it should you need to

Code:
Sub Borders_For_Non_Blank_Cells()

'   Puts a border round each cell that contains a value (Is not blank)
'   within the range of B1 to K150


Dim rng As Range
                
Set rng = Range("B1:K150") 'Change the range here if you need to go beyond

'Selects non blank cells
rng.SpecialCells(xlCellTypeConstants).Select

'puts borders all round the selection
    With Selection
    .borders(xlEdgeLeft).Weight = xlThin
    .borders(xlEdgeTop).Weight = xlThin
    .borders(xlEdgeBottom).Weight = xlThin
    .borders(xlEdgeRight).Weight = xlThin
    .borders(xlInsideVertical).Weight = xlThin
    .borders(xlInsideHorizontal).Weight = xlThin
    End With

'selects top left cell
    Range("A1").Select

End Sub
 
Upvote 0
Shorter version...
Code:
Sub Borders_For_Non_Blank_Cells()
Range("B1:K150").SpecialCells(xlCellTypeConstants).Borders.Weight = xlThin
Range("A1").Select
End Sub
 
Upvote 0
Here are screen shots of what I'm looking at.

1) What my screen looks like after I've pulled in the data I need: View image: pull
2) After my "Remove" code has removed extra space: View image: After pull and removal of extra rows
3) Goal of how I want it to look like with the borders: View image: Final Image

"Remove" Macro Code: View image: Remove Code

Keep in mind that the sections that say "Automated Module" and "Primary Production Functions" will dynamically change the number of rows it will have. That's where my issue is since I don't actually know how to code this since I'm not familiar with VBA at all. Let me know if you would need to see my "Pull in data" code.

Thanks for the help! I'm not sure if the above code posted in the earlier post will work but let me know if I should just try that.
 
Upvote 0
With your macro, it selects 4 ranges
B10:B28
B39:B58
B61:B77
B80:B95

are these always the maximum ranges, ie the data you pull will not go beyond these four ranges but may contain blank rows?
 
Upvote 0
It technically could, but I assumed it wouldn't (which I think may be a safe assumption for now, honestly). If you'd like to see that part of the macro, let me know and I can attach some images.

Thank you!
 
Upvote 0
No access to the PC at the mo, but I have tapped out some code on the iPad,

i hope it should work for you, but haven't tested it.

the macro should put an outside border round your ranges and then delete the empty rows.

Code:
Sub AddBorders_RemoveBlank ()

Range ("B10:B28").SpecialCells(xlCellTypeConstants).Select
With Selection
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
Range ("B10:B28").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete


Range ("B39:B58").SpecialCells(xlCellTypeConstants).Select
With Selection
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
Range ("B39:B58").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete



Range ("B61:B77").SpecialCells(xlCellTypeConstants).Select
With Selection
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
Range ("B61:B77").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete



Range ("B80:B95").SpecialCells(xlCellTypeConstants).Select
With Selection
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
Range ("B80:B95").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

End Sub

Someone may be along in a bit to simplify / Correct it, let us know how you get on with it.
 
Last edited:
Upvote 0
Just checking in on this, code needs modifying for the borders i imagine, as it needs to extend across to column K

my error, sorry
 
Upvote 0
Cooper, no problem at all. I tried the code but as you said, it didn't span out to column K. I'm not sure where to fix it in the code. Also, my first section "Automated Modules" is where the border should start but it's actually creating a new border around the first item "Auto Labeler" under Mainline System. Is there something wrong with the way I may be pulling in my data that counter acts what your code is doing? I can show you a screen shot if that explanation was not clear.

Thanks for the help.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top