CODEWITHSUNDEEP
vbaexceldynamically-generated
Ahmad A.
Ahmad A.

Reputation: 11

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

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!

Upvotes: 0

Views: 1449

Answers (1)

Bahamas
Bahamas

Reputation: 77

This will work:

Sub borders()

Dim lastRow As Long
Dim startOfNextSection As Range

'Section 1

'Check if there is only 1 row of data in Section 1
If IsEmpty(Range("b2")) Then
    lastRow = 1
Else
    lastRow = Range("b1").End(xlDown).Row
End If

With Range("B1", Cells(lastRow, 11))
    .borders(xlEdgeLeft).LineStyle = xlContinuous
    .borders(xlEdgeRight).LineStyle = xlContinuous
    .borders(xlEdgeBottom).LineStyle = xlContinuous
    .borders(xlEdgeTop).LineStyle = xlContinuous
End With

'Section 2

Set startOfNextSection = Cells(lastRow, 2).End(xlDown)

'Check if there is only 1 row of data in Section 2
If IsEmpty(startOfNextSection.Offset(1, 0)) Then
    lastRow = startOfNextSection.Row
Else
    lastRow = startOfNextSection.End(xlDown).Row
End If

With Range(startOfNextSection, Cells(lastRow, 11))
    .borders(xlEdgeLeft).LineStyle = xlContinuous
    .borders(xlEdgeRight).LineStyle = xlContinuous
    .borders(xlEdgeBottom).LineStyle = xlContinuous
    .borders(xlEdgeTop).LineStyle = xlContinuous
End With

'Section 3

Set startOfNextSection = Cells(lastRow, 2).End(xlDown)

'Check if there is only 1 row of data in Section 3
If IsEmpty(startOfNextSection.Offset(1, 0)) Then
    lastRow = startOfNextSection.Row
Else
    lastRow = startOfNextSection.End(xlDown).Row
End If

With Range(startOfNextSection, Cells(lastRow, 11))
    .borders(xlEdgeLeft).LineStyle = xlContinuous
    .borders(xlEdgeRight).LineStyle = xlContinuous
    .borders(xlEdgeBottom).LineStyle = xlContinuous
    .borders(xlEdgeTop).LineStyle = xlContinuous
End With

End Sub

Upvotes: 1

Related Questions