1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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.

Discussion in 'VBA Macros' started by Eloise T, Sep 7, 2017.

  1. Eloise T

    Eloise T Active Member

    Messages:
    656
    Code (vb):


    '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

     
  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,301
    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
    Chirag R Raval likes this.
  3. Eloise T

    Eloise T Active Member

    Messages:
    656
    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
    Chirag R Raval likes this.
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    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
    Chirag R Raval likes this.
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    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
  6. Eloise T

    Eloise T Active Member

    Messages:
    656
    Aha! Thanks to you both, Narayan and Luke.
  7. Marc L

    Marc L Excel Ninja

    Messages:
    3,225

    Hi !

    Second codeline is useless …
  8. Eloise T

    Eloise T Active Member

    Messages:
    656
    [​IMG]
    Got it. Thanks.
  9. Eloise T

    Eloise T Active Member

    Messages:
    656
    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?
  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    Rather...
    If ws.Cells(Rows.Count, 3).End(xlUp).Row > 4 Then

    As in, if last row is greater than 4 (ie >=5), then there's data.
    Chirag R Raval likes this.
  11. Eloise T

    Eloise T Active Member

    Messages:
    656
  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    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.
    Chirag R Raval likes this.
  13. Eloise T

    Eloise T Active Member

    Messages:
    656
    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?
  14. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    Correct.

    Narayan
    Chirag R Raval likes this.
  15. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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
  16. Eloise T

    Eloise T Active Member

    Messages:
    656
    Try this file.

    Attached Files:

  17. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    Dear Eloise T,

    I tested this code from your downloaded file..

    Code (vb):

    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
  18. Eloise T

    Eloise T Active Member

    Messages:
    656
    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.
  19. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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
  20. Eloise T

    Eloise T Active Member

    Messages:
    656
    Here's another option:

    Code (vb):

    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 (vb):

    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: Sep 16, 2017
  21. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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 (vb):

    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
  22. Debaser

    Debaser Active Member

    Messages:
    357
    You are setting font properties not range properties so it should be:

    Code (vb):
    With myrange.Font
  23. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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
  24. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    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
  25. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    Dear sir @NARAYANK991

    I modify as per your guidelines..

    Code (vb):

    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

Share This Page