Fill range in first empty row

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
948
Office Version
  1. 365
Platform
  1. Windows
Hi

I have got so far with the below code, which fills Column A in the first empty row
VBA Code:
Sub FillEndRowBlue()

If ActiveSheet.Name = "Training Log" Then
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
With Selection
.Interior.Color = RGB(197, 217, 241)

End With
Else
MsgBox "Cell fill does not work in this sheet", vbInformation, "Information"
End If
End Sub

I'm trying to fill columns A:F and I:J in the first empty row and I'm stuck!

Help would be much appreciated.

Many thanks!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,251
Office Version
  1. 365
Platform
  1. Windows
I'm trying to fill columns A:F and I:J in the first empty row and I'm stuck!
Like this?

VBA Code:
Sub FillEndRowBlue()
  If ActiveSheet.Name = "Training Log" Then
    Intersect(Rows(Cells(Rows.Count, 1).End(xlUp).Row + 1), Union(Columns("A:F"), Columns("I:J"))).Interior.Color = RGB(197, 217, 241)
  Else
    MsgBox "Cell fill does not work in this sheet", vbInformation, "Information"
  End If
End Sub
 
Solution

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
948
Office Version
  1. 365
Platform
  1. Windows
Hey Peter, that works perfectly and made me smile, thank you!

Best regards

Paul
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
948
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi

Would it be possible to add a bit more code so column 2 of the first empty row contains the text "OTHER" and column 9 contains the text "Indoor bike session, 60 mins." (without the commas)?

Many thanks!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,251
Office Version
  1. 365
Platform
  1. Windows
Like this?

VBA Code:
Sub FillEndRowBlue_v2()
  If ActiveSheet.Name = "Training Log" Then
    With Intersect(Rows(Cells(Rows.Count, 1).End(xlUp).Row + 1), Union(Columns("A:F"), Columns("I:J")))
      .Interior.Color = RGB(197, 217, 241)
      .Cells(2).Value = "OTHER"
      .Areas(2).Cells(1).Value = "Indoor bike session, 60 mins."
    End With
  Else
    MsgBox "Cell fill does not work in this sheet", vbInformation, "Information"
  End If
End Sub
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
948
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

WOW, thank you ever so much Peter, that's brilliant!

Looking at your solution, which part identifies the 2 columns, so I know how to apply this in other sheets/columns?

Thanks again!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,251
Office Version
  1. 365
Platform
  1. Windows
The range being coloured is in two 'areas', columns A:F and columns I:J

.Cells(2).Value = "OTHER"
This does not specifically identify which area so it defaults to the first area and the second cell in the first area is column B

.Areas(2).Cells(1).Value = "Indoor bike session, 60 mins."
This does specify the second area (cols I:J) and then specifies the first cell in that area (col I)

As usual there are other ways that we could have specified the cells to colour and/or put values in.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
948
Office Version
  1. 365
Platform
  1. Windows
Wow, that is clever 👍

Could I perhaps ask you for a more 'transparent' alternative that I can understand more easily and apply myself in a future situation please?

Thanks again Peter!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,251
Office Version
  1. 365
Platform
  1. Windows
Could I perhaps ask you for a more 'transparent' alternative that I can understand more easily and apply myself in a future situation please?
Sure, try this one

VBA Code:
Sub FillEndRowBlue_v3()
  Dim NextRow As Long
  
  If ActiveSheet.Name = "Training Log" Then
    NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & NextRow).Resize(, 6).Interior.Color = RGB(197, 217, 241)
    Range("I" & NextRow).Resize(, 2).Interior.Color = RGB(197, 217, 241)
    Range("B" & NextRow).Value = "OTHER"
    Range("I" & NextRow).Value = "Indoor bike session, 60 mins."
  Else
    MsgBox "Cell fill does not work in this sheet", vbInformation, "Information"
  End If
End Sub
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,610
Messages
5,765,412
Members
425,285
Latest member
andypandypoo

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