• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to interpret the difference between the two lines of code.

Eloise T

Active Member
Code:
'I understand   (Rows.Count, 3)   tells in which row to start.


Given:

Dim ws As Worksheet

  For Each ws In ThisWorkbook.Worksheets



'What's the difference between these two lines of code?

If ws.Cells(Rows.Count, 3).End(xlUp).Row > 2 Then

If ws.Cells(Rows.Count, 3).End(xlUp).Row >= 0 Then
 
The first will sometimes activate, the latter will always activate.

If we start at row at the last row in col 3 and go up, we'll find the last cell in col C with data. If the last cell with data is in row 1 or row 2, and all the rest are blank, then the first line of code will not run (row is <= 2).

The 2nd line of code is "bad", as the row of ANY cell will always be greater than 0, so it'll always run, regardless of where the last cell is.

PS. Nice signature. :p
 
In this case, the first two rows are headers, hence the (Rows.Count, 3).

So, the first line of code will drop to the "millionth" row and zoom upwards until it finds the "first" last row of data and tell the "rest of the code" to start here and do your job moving upwards?

The second line of code will essentially do the same thing but not really care if there's data to be worked on or not?

Did I get that right?

P.S. Working on spreadsheets, especially macros to make your job easier takes so much time, social media doesn't even take a back seat...it's not even in the car. :DD
 
Hi ,

The second check is invalid since we can never have row #0.

At the least , the line should be :

If ws.Cells(Rows.Count, 3).End(xlUp).Row > 1 Then

Both are equivalent if they are intended to recognize whether you have at least one row of data , based on which row your column headers are in.

If your headers are in row #1 , then your first row of data will be row #2. When you do .End(xlUp) , the cursor will either land on the last row of data , where ever that may be , or if there is no data , will land on the header row , where ever that may be.

Thus , if your header is in row #1 , the check should be :

If ws.Cells(Rows.Count, 3).End(xlUp).Row > 1 Then

If your header is in row #2 , the check should be :

If ws.Cells(Rows.Count, 3).End(xlUp).Row > 2 Then

and so on.

Narayan
 
Hi ,

Rows.Count, 3 signifies the last row of the worksheet , and column 3.

This will therefore check for the last row of data in column C.

Narayan
 
Hi !

Second codeline is useless …
iu

Got it. Thanks.
 
Hi ,

The second check is invalid since we can never have row #0.

At the least , the line should be :

If ws.Cells(Rows.Count, 3).End(xlUp).Row > 1 Then

Both are equivalent if they are intended to recognize whether you have at least one row of data , based on which row your column headers are in.

If your headers are in row #1 , then your first row of data will be row #2. When you do .End(xlUp) , the cursor will either land on the last row of data , where ever that may be , or if there is no data , will land on the header row , where ever that may be.

Thus , if your header is in row #1 , the check should be :

If ws.Cells(Rows.Count, 3).End(xlUp).Row > 1 Then

If your header is in row #2 , the check should be :

If ws.Cells(Rows.Count, 3).End(xlUp).Row > 2 Then

and so on.

Narayan
So if my headers took up rows 1-4 then the check should be :

If ws.Cells(Rows.Count, 3).End(xlUp).Row > 5 Then

Correct?
 
So, up to row 4 is header correct?

So if last non empty row check does not return 5 or greater then there's no data to perform operation on.
 
So if the last data-filled row does not return 5 or greater with some row counter that's going on, assuming from the bottom up?...then the row counter will stop allowing the code to perform its duties if the counter is less than 5, correct?

...and that's written like: If ws.Cells(Rows.Count, 3).End(xlUp).Row > 4 Then

Correct?
 
So if the last data-filled row does not return 5 or greater with some row counter that's going on, assuming from the bottom up?...then the row counter will stop allowing the code to perform its duties if the counter is less than 5, correct?

...and that's written like: If ws.Cells(Rows.Count, 3).End(xlUp).Row > 4 Then

Correct?
Hi ,

Correct.

Narayan
 
Dear all,

Amazing concept to learn & understand...

Can you give example code
To perform as make all text italic of rows if found data after row 4 (rows count ate dynamic)
?

Regards,

Chirag Raval
 
Dear all,

Amazing concept to learn & understand...

Can you give example code
To perform as make all text italic of rows if found data after row 4 (rows count ate dynamic)
?

Regards,

Chirag Raval
Try this file.
 

Attachments

  • Chandoo - CF for Italics.xlsm
    15.8 KB · Views: 4
Dear Eloise T,

I tested this code from your downloaded file..

Code:
Sub Covert2Italics()

    With Range("C4:C504")    ' Range covers C4 to C504 and is easily changed.
               
'      This line removes previously existing Conditional Formats.
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=SUMPRODUCT(ISNUMBER(SEARCH(D40,C3))+0)"
                 
'      M A K E  C H A N G E S  H E R E
        .FormatConditions(.FormatConditions.Count).Font.ColorIndex = 1            'Black font
        .FormatConditions(.FormatConditions.Count).Font.Bold = False
        .FormatConditions(.FormatConditions.Count).Font.Italic = True            'Italics on
        .FormatConditions(.FormatConditions.Count).Font.TintAndShade = 0
                   
'      .FormatConditions(.FormatConditions.Count).Interior.ColorIndex = 3        'Red: the background color
             
        .FormatConditions(.FormatConditions.Count).StopIfTrue = False

    End With

    Beep    'Let's you know it's done.

End Sub

but its work on fixed range only.

in your first post. (in continuation)
"If ws.Cells(Rows.Count, 3).End(xlUp).Row > 4 Then" (modify 2 to 4)

code check if first 4 rows have something..? if not have....nothing to do..

if have ,that's definitely should header rows... also nothing to do till now..
because we want only code work after row no 4...

& if more then 4.... then row no 5 to end of data should be "Italic"
(what you want after row no 4? like 5 to End of data...)

require some logical code to
work some thing below all rows from row no 4...

how to do this...?

Regards,
Chirag Raval
 
First of all,
I am (figuratively) a blind man showing another blind man where to find food.
I have no business teaching another person how to do VBA.

Having said and understood that,
Your original question was not clearly understood and I took a shot in the dark.
Your last question was not clearly understood either as to what you are wanting to do.

The code I sent, "sets" a Conditional Format to (as you correctly pointed out) a limited range, i.e. ("C4:C504"). If you need to expand that range, change the range from what it is to (A5:H5000") or whatever you need it to cover.

You also have the option to "Not" declare a range and specify from, e.g. Cell A5, until you run out of data (if you don't know what the end will be.)

If you like, restate your question or what you need to do and we'll go at this from another direction or fix the existing code. Hopefully others here on Chandoo will see this and add their Expert advice.
 
Dear All,

if any one hurts...then I want to say sorry..
I really forgot when replying Eloise T that question asking to Experts instead of Elois T that my mistake..

I just want to know if above checks complete that checks if first 4 rows filled or not? if filled then that should be header rows...but I want to know that if is this check is complete what can we do on 5th & rest rows which are data rows...?
for example ...like how can make all rows "italic" after 4th row (start from 5th row)

I can start new thread ,.. but I think if this currently running thread start from first checks how many rows there are header rows...then this currently running thread can be good point for further more...

This try or asking is just purpose of learning further more...& also try to give touch of completeness to this thread...that's it...

Hope experts can help here..

Regards,
Chirag Raval
 
Dear All,

if any one hurts...then I want to say sorry..
I really forgot when replying Eloise T that question asking to Experts instead of Elois T that my mistake..

I just want to know if above checks complete that checks if first 4 rows filled or not? if filled then that should be header rows...but I want to know that if is this check is complete what can we do on 5th & rest rows which are data rows...?
for example ...like how can make all rows "italic" after 4th row (start from 5th row)

I can start new thread ,.. but I think if this currently running thread start from first checks how many rows there are header rows...then this currently running thread can be good point for further more...

This try or asking is just purpose of learning further more...& also try to give touch of completeness to this thread...that's it...

Hope experts can help here..

Regards,
Chirag Raval
Here's another option:

Code:
Sub Italics1()

If Cells(Rows.Count, 3).End(xlUp).Row > 4 Then
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Italic"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End If
Beep
End Sub

...or slimmed down...

Code:
Sub Italics1()

If Cells(Rows.Count, 3).End(xlUp).Row > 4 Then
    With Selection.Font
 
         .FontStyle = "Italic"
      
     End With
End If
Beep
End Sub
 
Last edited:
Dear @Eloise T

Thanks for your efforts...I appreciate..
but I want to ask an experts ..that I try to make dynamic range
from Rows 5 & below as per below..to process

Code:
Sub Row_4Below_All_Italics1()
Dim myrange As range
myrange = ActiveSheet.range("C5") & Cells(Rows.cound, 3).End(xlUp).Row
With ActiveSheet
If Cells(Rows.count, 3).End(xlUp).Row > 4 Then
    With myrange
        .Name = "Calibri"
        .FontStyle = "Italic"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
   
End If
Beep
End With
End Sub

but Error Code "438"
"Dose not support this type of property or method"

displayed..

Can anyone help here?

Regards,
Chirag Raval
 
You are setting font properties not range properties so it should be:

Code:
With myrange.Font
 
Dear Sir Debaser,

Thanks ...I change as per you r guideline..

but now same error (that previously already appear on )
"myrange = ActiveSheet.range("C5") & Cells(Rows.cound, 3).End(xlUp).Row"

may be I wrongly referencing range ..

Please help..

Regards,
Chirag Raval
 
Hi ,

If you have declared myrange as a variable of type Range , then the following assignment is wrong :

myrange = ActiveSheet.range("C5") & Cells(Rows.cound, 3).End(xlUp).Row

because the expression on the right hand side will return the number of a row , which is of type Long.

The correct assignment will be :

Set myrange = ActiveSheet.Range("C5" , Cells(Rows.Count, 3).End(xlUp))

Narayan
 
Dear sir @NARAYANK991

I modify as per your guidelines..

Code:
Sub Row_4Below_All_Italics1()
Dim myrange As range
'myrange = ActiveSheet.Rows(3, 3) , Cells(Rows.cound, 3).End(xlUp).Row
'myrange=ActiveSheet.range("C3").row,Cells(Rows.cound, 3).End(xlUp).Row
Set myrange = ActiveSheet.range("C5", Cells(Rows.count, 3).End(xlUp))
        If Cells(Rows.count, 3).End(xlUp).Row > 4 Then
      With myrange.Font
          .Name = "Calibri"
        .FontStyle = "Italic"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
      End If
Beep
End With
End Sub

nothing happened ...code constant alternating errors displaying
"End if" without "if"
"end with without with block"

Please help..

Regards,
Chirag Raval
 
Back
Top