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

[ahmadazhan]

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!

 

[Michael M]

This will border from "B" to "K"
I don't follow what criteria gets the row deleted.!!!

Sub AddBorders_RemoveBlank()
Range("B10:K28,B39:K58,B61:K77,B80:K95").SpecialCells(xlCellTypeConstants).Borders.Weight = xlThin
End Sub

 

[cooper645]

Which cell is it in the top left corner of the range you would like the border to start? (the one thats wrong)

 

[cooper645]

This will border from "B" to "K"
I don't follow what criteria gets the row deleted.!!!

Sub AddBorders_RemoveBlank()
Range("B10:K28,B39:K58,B61:K77,B80:K95").SpecialCells(xlCellTypeConstants).Borders.Weight = xlThin
End Sub

with regards to the row deleting, i took it that the OP's code

[COLOR=#ff0000]Range ("B10:B28").Select[/COLOR]
[COLOR=#0000ff]Selection.SpecialCells(xlCellTypeBlanks).Select[/COLOR]
[COLOR=#00ff00]Selection.EntireRow.Delete[/COLOR]

simply Selected the range of the first column of data pulled, for each segment
then selected those cells in that range that were blank
and deleted that row

 

[ahmadazhan]

B7 is the top left corner of the first border.

 

[cooper645]

try the following;

Sub AddBorders_RemoveBlankRows()


Range("B7:K28,B39:K58,B61:K77,B80:K95").SpecialCells(xlCellTypeConstants).Select
With Selection
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With


Range("B7:B28,B39:B58,B61:B77,B80:B95").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

fingers crossed this does as you need it to

Coops

 

[ahmadazhan]

Cooper, it didn't work
It's doing something similar and still not ranging out to the K column. I can post up the new code I'm trying shortly. Let me know if you come up with anything else.

Thanks! This is a pain in the butt...

 

[cooper645]

try this;

Sub AddBorders_RemoveBlankRows()

Range("B7:K7").Borders(xlEdgeTop).Weight = xlThin
Range("B58:K58").Borders(xlEdgeBottom).Weight = xlThin
Range("B7:B58").Borders(xlEdgeLeft).Weight = xlThin
Range("K7:K58").Borders(xlEdgeRight).Weight = xlThin


Range("B39:K39").Borders(xlEdgeTop).Weight = xlThin
Range("B28:K28").Borders(xlEdgeBottom).Weight = xlThin
Range("B39:B28").Borders(xlEdgeLeft).Weight = xlThin
Range("K39:K28").Borders(xlEdgeRight).Weight = xlThin


Range("B61:K61").Borders(xlEdgeTop).Weight = xlThin
Range("B77:K77").Borders(xlEdgeBottom).Weight = xlThin
Range("B61:B77").Borders(xlEdgeLeft).Weight = xlThin
Range("K61:K77").Borders(xlEdgeRight).Weight = xlThin


Range("B80:K80").Borders(xlEdgeTop).Weight = xlThin
Range("B95:K95").Borders(xlEdgeBottom).Weight = xlThin
Range("B80:B95").Borders(xlEdgeLeft).Weight = xlThin
Range("K80:K95").Borders(xlEdgeRight).Weight = xlThin


Range("B7:B28").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B39:B58").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B61:B77").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B80:B95").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


End Sub

 

[Michael M]

@ahmadazhan
Make sure the cells you think are blank.....ARE really Blank !!!

 

[cooper645]

It seemed to be working for me,

is is there a possibility you can paste your full codes that work, and if it's not a security problem a sample file of the scraped data you are trying to clean up with this macro. This would help greatly in getting a working code. Or if it's off the net then a link to the site with codes?