Highlight columns from same point, but with variable number of rows

[Surreybloke]

Hi,

I've got a spreadsheet where the columns don't change, but the number of rows does. As part of a larger macro (which I already have setup) I need to have the first part of the macro highlighting from column A3 and B3 down to the last row of data for both columns, then I need to replace the zeroes with a blank cell instead.

I'm sure this will be a piece of cake for someone, but your help will be much appreciated!

 

[Peter_SSs]

.. from column A3 and B3 down to the last row of data for both columns, ..

I'm not sure but I'm taking that to mean there could be a different number of rows used in each of those 2 columns and you want each column highlighted to the end of its data.
If so, try this with a copy of your data. Post back with more details if it is not what you want.

Sub DoIt()
  Range("A3", Range("A" & Rows.Count).End(xlUp)).Interior.Color = vbYellow
  Range("B3", Range("B" & Rows.Count).End(xlUp)).Interior.Color = vbYellow
  Columns("A:B").Replace What:="0", Replacement:="", LookAt:=xlWhole
End Sub

 

[Surreybloke]

Hi Peter,

Yes that's correct, although the number of rows whilst different from month to month, will always be the same for each of the two columns.

I will add the code to my macro and let you know how I get on.

Many thanks for your help

 

[Peter_SSs]

.. although the number of rows whilst different from month to month, will always be the same for each of the two columns.

In that case we don't need to deal with the columns individually. Try

Sub DoIt()
  With Range("A3", Range("B" & Rows.Count).End(xlUp))
    .Interior.Color = vbYellow
    .Replace What:="0", Replacement:="", LookAt:=xlWhole
  End With
End Sub

 

[Surreybloke]

I've made some amendments to your code as I need it to do a couple of extra things in order for it to work. The workbook has multiple worksheets, the one which I need the macro to run in is called "Subs" so I've added a bit of code at the start which is meant to tell macro where to run. Also, I need the two columns (I've changed them to the actual column references for my spreadsheet now) to be copied and paste special values to set the zeroes back to values rather than a formula, which is what they are at. However, for some reason, the code is activating for the worksheet where I have created the macro buttons, so I'm not quite sure why that is happening. I also don't really need the cells to be changed to yellow, but that is a minor point for now.

This is the amended code:

Sub RemoveZeroes()


' Set to run on "Data" sheet
Set ws = Sheets("Subs")
Columns("DH:DI").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("DH3").Select
With Range("DH3", Range("DI" & Rows.Count).End(xlUp))
.Interior.Color = vbYellow
.Replace What:="0", Replacement:="", LookAt:=xlWhole
End With
End Sub

 

[Peter_SSs]

.. the one which I need the macro to run in is called "Subs" so I've added a bit of code at the start which is meant to tell macro where to run.

Although you have set ws = Sheets("Subs"), you have not then used 'ws' anywhere in your code.
Also, when posting code, please use Code Tags to preserve the indentation (makes the code easier to read/debug) - my signature block below explains how.

See if this does what you want.

Sub RemoveZeroes()
  With Sheets("Subs")
    With .Range("DH3", .Range("DI" & .Rows.Count).End(xlUp))
      .Value = .Value
      .Replace What:="0", Replacement:="", LookAt:=xlWhole
    End With
  End With
End Sub

 

[Surreybloke]

That works perfectly, thanks a lot for your help.

 

[Peter_SSs]

That works perfectly, thanks a lot for your help.

You are very welcome. Thanks for the follow-up.