VBA, count the number of unhidden rows

Hello

This way it should work better

 Private Sub CommandButton3_Click() Dim R As Double Dim l As Double For l = 1 To Cells(65536, 1).End(xlUp).Row If Not Rows(l).Hidden Then R = R + 1 Next l MsgBox ("there are " & R & " visible rows in column A") End Sub 

It’s the visible rows that are counted, not the non-empty cells..
--

always zen

Thank you so much for your solution,

I will work on it because I am a beginner in VBA

THANK YOU SOOO MUCH AGAIN!

Honestly, your solution works better than the program that was given to me on a forum
yours only counts the visible ones, that’s excellent!!

if I have another issue, can you help me?

thanks

Hello

Thank you for your message and isn't it normal to provide code that meets the needs?

If I have another problem, can you help me?

No problem, but soon it will be you helping others!

--

always zen

Hello,

I'm getting back to you, hoping I'm not asking too much

So, I have an issue with inputBox; when I click cancel or if I don't enter anything in inputBox, VBA activates the debugger,
I would like it to close the inputBox if I cancel,

you can see, THAAAAANKS

Private Sub CommandButton2_Click()

Range("a1:I30").Interior.ColorIndex = xlNone

Dim msg As Double

msg = InputBox("what is the moving average? ")

'If msg = "" Then Exit Sub

'Dim msg
'msg = InputBox
'If msg = vbCancel Then
'Exit Sub
'End If

Dim c As Range

For Each c In ActiveSheet.Range("a1:I30")

If c < msg And c <> "" Then
c.Interior.Color = RGB(168, 168, 168)
End If

Next

End Sub

Hello

I suggest you handle the cancellation like this:

On Error Resume Next ' if there is an error, proceed to the next line msg = InputBox("what is the moving average? ") If msg = 0 Then Exit Sub ' if "Cancel" is used, exit the macro On Error GoTo 0 ' error handling is restored 

You can also do it by changing your reservation

Dim msg As  Variant msg = InputBox("what is the moving average? ") If msg = "" Then Exit Sub ' if "Cancel" is used, exit the macro 

--

always zen

re

I tested what you gave me, but I'm still encountering issues, either it starts debugging or it displays the input box twice

my task is to color cells less than x%

thank you for your help

Hello

I provided you with 2 options: you choose one or the other, not both.

The simplest is to pass "msg" by varying.
--

always zen

re hello

I put msg as a variant, on the input box level it works perfectly, but it's no longer respecting the condition and coloring all the cells, it doesn't seem to understand,

thank you

hello

With this, it should work better.

For Each c In ActiveSheet.Range("a1:I30") If c < Val(msg) And c <> "" Then c.Interior.Color = RGB(168, 168, 168) Else c.Interior.Color = xlNone End If Next 

When you enter a value in the input box, it is text and therefore it needs to be converted to a number for comparison.
In Excel formulas, this happens sometimes but not in VBA.

It is also safer to remove the color if the condition is not met.
--

always zen

Hello, I just took the test with

Val(msg) and On Error Resume Next, IT WORKS GREAT!! PERFECT

So 'val', as you explained to me, translates the text into numeric (since it's from the inputBox, that's it if I'm not mistaken)

and 'On Error Resume Next' allows moving to the next line if the user makes a mistake and inputs text (due to a typing error)

However, no need for the else, otherwise in the loop it colors the entire working range after meeting the condition

Otherwise, I was suggested this: If ((CDec(c.Value) < CDec(msg)) And (CStr(c.Value) <> "")) Then, I don't know what that means and copying and pasting it didn't work

I just want to thank you and say see you soon

Have a great day!

```vba
Private Sub CommandButton1_Click()
Dim var As Integer
var = 2
For var = 2 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(var, 2) > 100 Then
Range(Cells(var, 1), Cells(var, 2)).Font.Bold = True
End If
Next
End Sub
```

Hello

Maybe with another type of loop

Private Sub CommandButton1_Click() Dim var As Integer var = 2 Do While Cells(var, 1) <> "" If Cells(var, 2) > 100 Then Range(Cells(var, 1), Cells(var, 2)).Font.Bold = True End If var = var + 1 Loop End Sub 

You progress your row index and stop if the cell is empty: adapt with your table!
--

always zen

Hi my friend

back

I would like please

to convert a date (dd/mm/year) into the corresponding week

thank you

Hello

With the year in A1, this formula is more effective than the WEEKNUM function, which is unreliable in Europe at least.

=INT(MOD(INT((A1-2)/7)+0,6;52+5/28))+1

--

Knowledge is the only material that increases when shared. (Socrates)

Hello,

well thank you thank you and thank you!!

because I spent the whole evening yesterday, from 5 PM to 11 PM (of course not continuously) and I found nothing on Google

great!!

can you tell me how you found it so I can improve my knowledge in IT;

thanks again

and have a great day!

see you soon

Hello,

can you help me please

I would like to color the cells based on the content of the cell in column B

my data range is: "C2:L27", and my conditions are based on column B

I wrote some code but it doesn't work

here it is:

Private Sub CommandButton1_Click()

Dim c As Range
Dim var As Integer
var = 2

For Each c In ActiveSheet.Range("C2:L27")

If c <> "" And Cells(var, 2) = mp Then
c.Interior.ColorIndex = 4
End If
var = var + 1
Next

If c <> "" And Cells(var, 2) = mc Then
c.Interior.ColorIndex = 3
End If
var = var + 1

If c <> "" And Cells(var, 2) = df Then
c.Interior.ColorIndex = 1
End If
var = var + 1

End Sub

Hello

If I understood correctly...

Private Sub CommandButton1_Click() Dim c As Range Dim var As Integer var = 2 For Each c In ActiveSheet.Range("c2:l27") If c <> "" And Cells(c.row, var) = mp Then c.Interior.ColorIndex = 4 End If Next If c <> "" And Cells(c.row, var) = mc Then c.Interior.ColorIndex = 3 End If If c <> "" And Cells(c.row, var) = df Then c.Interior.ColorIndex = 1 End If End Sub

--

Knowledge is the only thing that increases when shared. (Socrates)

Honestly, you replied to me, I'm so happy!!

Yes that's it, actually I want to color my cells in the range ( Range("c2:l27") ) according to the content of the cells in column b2

I've got your code but it’s not working,

Please help me, thaaaaaaank youuuuu

How can I send you the Excel file otherwise?

If you need more explanation, I'm online, thank you
because the code you sent me seems very correct, but why isn't it responding?