Tried to put last row into the ranges

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Not sure why these ranges will not work with last rows added to them.

VBA Code:
          Private Sub Add_Break_Lines_Click()

                
                Dim Com As ComboBox
                Dim ws As Worksheet
                Dim LastRow As Integer
   
                   
              
  
              
               
                Set ws = Application.Workbooks("Automated Cardworker.xlsm").Worksheets("Job Card Master")
                Set Com = Me.Add_Break_Lines
                LastRow = ws.Range("A" & Rows.Count).End(xlUp).row
               
               ws.Range("P13:P299").ClearContents

               
                With ws
               
               
              
                Select Case Com.Value
                    Case ("Break Lines 1 Page Job Card")
                        Color .Range("A13:Q & Lastrow")
           
                    Case ("Break Lines 2 Page Job Card")
                        Color .Range("A13:Q61")
                        Color .Range("A66:Q & Lastrow")
              
                    Case ("Break Lines 3 Page Job Card")
                         Color .Range("A13:Q61")
                        Color .Range("A66:Q122")
                        Color .Range("A127:Q & Lastrow")
                       
                    Case ("Break Lines 4 Page Job Card")
                        Color .Range("A13:Q61")
                        Color .Range("A66:Q122")
                        Color .Range("A127:Q183")
                        Color .Range("A188:Q & Lastrow")
           
                    Case ("Break Lines 5 Page Job Card")
                        Color .Range("A13:Q61")
                        Color .Range("A66:Q122")
                        Color .Range("A127:Q183")
                        Color .Range("A188:Q244")
                        Color .Range("A249:Q & Lastrow")
                End Select
               
      
            End With
End Sub
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
just a screen capture will do
This shows the lines are filling into the end of the sheet they need to stop when the last row is filled in. Example Row 51
Worksheet Image Share.JPG
 
Last edited:

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,048
Office Version
  1. 2010
Platform
  1. Windows
Appears to me you would want LastRow to be from column C rather than column A

and the link to your workbook on dropbox is broken
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Please try the link below.
I`ve tried column c but it still does not work.

Mt

 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,048
Office Version
  1. 2010
Platform
  1. Windows
It doesn't find the desired last row using column A because SUNDRIES and Job Card 0000A are both in column A.
It doesn't find the desired last row using column C because TOTAL BODY SHOP HOURS is in column C.
Move TOTAL BODY SHOP HOURS to column A and use horizontal alignment of 'Center across cells' to center it across columns A,B and C and your label will appear the same and using column C will determine lastrow to be 151
 
Solution

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Sorry yes, I meant to say with this that I have moved the text in column C to column A and that does solve that issue above.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,001
Messages
5,622,126
Members
415,878
Latest member
jjj12345

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