VBA to Border a Range

[GreatOffender1]

Range is dynamic but always starts at K1 and includes column L. My problem is setting the range. 101 tutorials on setting the range for borders "if" you know the range but I don't because it will be changing on each instance. This is only a macro to eliminate unnecessary spaces and then split the remaining number (not the problem).

Problem is choosing the range of column K1 to L and bordering those cells. At present the only working method I have is "used range" and that creates boarders around unwanted columns. I know you would love to see the code but it doesn't work so I'm not sure what you want to see? My problem seems to be setting the range.

& Rows.Count).End(xlUp) = not working
& LastRow) = not working

Thank you,

 

[Joe4]

Welcome to the Board!

Can you explain how we can determine where the last row is?
Can we look at a certain column, to see what the last cell in that column is with data, and use that as our last row?
If so, which column would be the best one to look at for that?

 

[GreatOffender1]

All columns are the same length. Original data pasted into the worksheet covers columns A-F. This data must remain visible, so the macro creates a second grid in columns I-O. There is one more column than the original because original column C is split into 2 columns. The original has a Medium black border but after the macro runs the new grid does not have borders around columns K and L. Because the input is dynamic there is no set number of rows. Does that cover it?

 

[Joe4]

This should dynamically set a range variable for the range your want to apply your formatting to:

    Dim LastRow As Long
    Dim rng As Range
   
'   Find last row in column A with data
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Set range in columns K and L to format
    Set rng = Range("K1:L" & LastRow)

 

[GreatOffender1]

I swear I've tried that one before but I am not easily deterred. I'll give it a try and check back.

Thank you,

 

[GreatOffender1]

Yes and no. I knew this looked familiar and when I look back at my notes I see I have tried something similar. On its own it works, however the macro takes the original data and copies it to row "I1:N1" so there is a 2 column gap between the original grid and the copied grid and I believe that is my problem. Stand alone it works just fine but that gap is causing problems. I was thinking about eliminating the gap until "after" I added the boarders and just insert 2 blank rows?

I am doing this from a work laptop to my ability to upload anything doesn't work. The original data grid is left to compare to my copy starting at column "I" so I left the blank columns as a visual space between the 2.

 

[Joe4]

I am confused by your follow-up. What exactly is the issue?

Is it not calculating the correct last row correctly?
If that is the issue, it may be because of the column we are using (which is why I asked before).
If this is the case, then column A is NOT a good column to use to find the last row with data and we should use a different one.

So if we should be using column I instead, then it should look like:

    LastRow = Cells(Rows.Count, "I").End(xlUp).Row

If that is not the issue, I am confused how blank columns come intp play, as I thought you said you wanted to apply this to columns K and L.
Is it the columns that are variable too, just rows, or both?
If columns, is it always the last two columns that you want?

Perhaps you could show us some screen prints of your data and expected results.

 

[GreatOffender1]

Sorry for the confusion but I had to leave mid post and then went back so it was somewhat circular. I have tried accounting for the gaps and adjusted my "LastRow" but had no luck. After once again researching this topic I came across my own thread and went back to try again and it worked. For no apparent reason it worked as advertised which confuses me. Your solution, which works, was tried several times prior with no luck. Lottery tonight?

Thanks again for all your help and sticking with the thread.

 

[Joe4]

You are welcome.
Glad I was able to help!