CODEWITHSUNDEEP
vbaexcel
Carlos80
Carlos80

Reputation: 433

Count at specific cell

I know this is going to be an easy tweak, however I have tried a number of variations and have also been on Google and this forum with little luck.

The code I have so far gives me a row count for column B, with a return value of 225. However I want the count to begin at cell "B17" to return a value of 209. This is because B17 is the beginning of a table within my spreadsheet that will hold a variable amount of data.

The code so far is:

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "B").END(xlUp).row
End With

Thanks in advance

[Results when using the code

Ultimate Result

Sub Macro1()

Dim startRow As Double
Dim lastRow As Double
Dim procCol As Double
Dim i As Double

'I will preface this with you should not use activesheet
'you should declare:
'Dim thisWS As Worksheet
'Set thisWS = Thisworkbook.Worksheets("yourworksheetname")
'and use thisWS where you see Activesheet

startRow = ActiveSheet.Range("K16").row
procCol = ActiveSheet.Range("K16").Column

lastRow = ActiveSheet.Cells(Rows.Count, procCol).END(xlUp).row

'for RGB use a tool like
'https://www.w3schools.com/colors/colors_picker.asp


  For i = startRow To lastRow 'this will run through the rows, it's dynamic
   If ActiveSheet.Cells(i, procCol).Value = ActiveSheet.Cells(i + 1, procCol) Then

            Range(ActiveSheet.Cells(i, 2), ActiveSheet.Cells(i, 20)).Interior.Color = RGB(220, 230, 241) 'this is light blue
            Range(ActiveSheet.Cells(i + 1, 2), ActiveSheet.Cells(i + 1, 20)).Interior.Color = RGB(255, 255, 255) 'this is light blue

   Else 'Do nothing

   End If
Next i

End Sub

The only thing I have changed is the reference cell from B to K, extended the range so that it fills a row not a cell and updated the colour?

Upvotes: 1

Views: 123

Answers (4)

Wookies-Will-Code
Wookies-Will-Code

Reputation: 735

"Thanks this actually seems to work. Ultimately I'm trying to find the last row in my table (as it varies). The next stage to work out is to try and format the data so that I can put a different fill colour every time a certain value matches the row below. The idea being that the data is more readable by seeing the rows separated by colour. The issue is sometime 2 rows might match, sometimes 10 etc. – Carlos80 8 hours ago"

This code will do what you want, if you want a different color for each selection the code will have to be modified slightly. It will dynamically range the column you hard code, you can also change the column or the starting row of the column easily. Check it out, it is simple but will do what you want based on B17 to the end of the column with data.

**Updated to interleave your colors if new matches are right on top of one another ****

Option Explicit

Sub Macro1()


Dim startRow As Double
Dim lastRow As Double
Dim procCol As Double
Dim i As Double

'I will preface this with you should not use activesheet
'you should declare:
'Dim thisWS As Worksheet
'Set thisWS = Thisworkbook.Worksheets("yourworksheetname")
'and use thisWS where you see Activesheet

startRow = ActiveSheet.Range("B17").Row
procCol = ActiveSheet.Range("B17").Column

lastRow = ActiveSheet.Cells(Rows.Count, procCol).End(xlUp).Row

'for RGB use a tool like
'https://www.w3schools.com/colors/colors_picker.asp


  For i = startRow To lastRow 'this will run through the rows, it's dynamic
   If ActiveSheet.Cells(i, procCol).Value = ActiveSheet.Cells(i + 1, procCol)  Then

            ActiveSheet.Cells(i, procCol).Interior.Color = RGB(0, 204, 255) 'this is light blue
            ActiveSheet.Cells(i + 1, procCol).Interior.Color = RGB(0, 204, 255) 'this is light blue

   Else 'Do nothing

   End If
Next i

End Sub

Cheers, Wookie

Upvotes: 1

TomJohn
TomJohn

Reputation: 747

If your table is Excel Data Table (Insert > Table) you can try to count rows like this:

Set tbl = Sheets("Your_Sheet").ListObjects("Your_table")
rows_no = tbl.DataBodyRange.Rows.Count
Debug.Print rows_no

Upvotes: 0

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

if you need to count rows between 17 (included) and current cell, it is really simple: 

LastRow = Range(ActiveSheet.Range("A17"), Range("A" & ActiveSheet.Rows.Count).End(xlUp)).Rows.Count

Upvotes: 2

Vityata
Vityata

Reputation: 43585

Have you tried like this:

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row - .Range("B17").Row
End With

It really depends what you want actually. But in the case above you would simply remove 17 from the result. Consider adding +1 to LastRow to get what you actually want.

Like this LastRow = LastRow +1

Upvotes: 1

Related Questions