VBA code for indeterminate ranges containing blank rows

[jardenp]

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:

'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

Range("A2:K" & Range("A2").End(xlDown).Row).Select

to replace

Range("A2:K43").Select

but I get an error. I suspect because the data cells in column A aren't contiguous.

Any thoughts?

 

[HalfAce]

Try using xlUp instead of down. (and you don't need to select the range to work with it.) ie.

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]

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:

'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!)