VBA code for indeterminate ranges containing blank rows

jardenp

Active Member
Joined
May 12, 2009
Messages
369
I have "entries" that consist of a row of data and then a blank row below. The first entry is in A2:K3. The number of entries is indeterminate. I have formatting (borders and such) that I want to copy from the first to all the other entries--A4:K5, A6:K7, etc.

My modified code from the macro recorder is:
Code:
'Copy Box Format to Remainder of Entries
    Range("A2:K3").Select
        Selection.Copy
    Range("A2:K43").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
I normally would use something along the lines
Code:
Range("A2:K" & Range("A2").End(xlDown).Row).Select
to replace
Code:
Range("A2:K43").Select
but I get an error. I suspect because the data cells in column A aren't contiguous.

Any thoughts?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Try using xlUp instead of down. (and you don't need to select the range to work with it.) ie.
Code:
Range("A2:K3").Copy
Range("A2:K" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
  SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Does that help?

BTW, your original code (the xlDown) did not give me an error, it just selected rows 2:4, which is the expected result for your data layout. (but I suspect not what you're looking to do.)
 

jardenp

Active Member
Joined
May 12, 2009
Messages
369
I apologize: I misspoke (or mistyped?). I didn't mean "error," just that it didn't do what I wanted.

Your code works great. Thank you.

The only problem is that the bottom border on the last entry doesn't transfer. It's not really a "problem," but would you know how to fix that?

The formatting I applied earlier in the code is:
Code:
'Format Line Boxes
    Range("A2:K3").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    
    'Underline Data
    Range("A2:J2").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
Thanks! (And if you're an Alaskan who fishes, I'm jealous!)
 

Forum statistics

Threads
1,136,353
Messages
5,675,285
Members
419,559
Latest member
BraytonM

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
Top