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

Clarification please...

Within the Cells object, the 2nd argument specifies column number. In this case, the 6 refers to col 6, aka col F.

The highlighted 2 is just a plain 2. The whole line says to go to last row in col F, and then go up (the End(xlUp) bit) until it finds something. Check to see if the row where we found something is higher than 2.

Or, in plainer English: Is the last item in col F after col 2?
 
Dear all,

May be we can say or check by code
Is there column F (6) have more then 2 rows with data?
If more then 2 ,then do something .

Regards,

Chirag Raval
 
To get specific help while in the Visual Basic Editor (VBE), with cursor in or next to the keyword/command, press F1. Press F2 to browse all objects, methods, and properties.

The help is not the best on this one. Why choose Cells() over Range() one might ask. There are trade-offs to both.

1. Cells() is not known to intellisense. If you type a period after a word, if available, you get a dropdown list to choose from. That is if Tools > Options >Editor > Auto List Member, is checked as is the default.

2. Range() allows intellisense to work if option is set. You may have to use string concatenation as Alan did to build your range's input values. Avoid doing lots of string concatenation. That is a slow process. For a few concatenations, the slower speed is not noticed by humans.

3. Cells() lets you use number or index values for rows, the 1st input, and columns, the 2nd input, as you can see while typing it. Unknown to some is that you can also use the column letter. e.g.
Code: 
Sub Demo()
  Dim ws As Worksheet, r As Range
  For Each ws In ThisWorkbook.Worksheets
    Set r = ws.Cells(Rows.Count, "F").End(xlUp)
    If r.Row > 2 Then
      Debug.Print r.Address(external:=True)
      Else
    End If
  Next ws
End Sub
 
Last edited:
Luke M said:
Within the Cells object, the 2nd argument specifies column number. In this case, the 6 refers to col 6, aka col F.

The highlighted 2 is just a plain 2. The whole line says to go to last row in col F, and then go up (the End(xlUp) bit) until it finds something. Check to see if the row where we found something is higher than 2.

Or, in plainer English: Is the last item in col F after col 2?
Click to expand...
"Is the last item in Column F after Column 2 (B)?
 
Kenneth Hobson said:
To get specific help while in the Visual Basic Editor (VBE), with cursor in or next to the keyword/command, press F1. Press F2 to browse all objects, methods, and properties.

The help is not the best on this one. Why choose Cells() over Range() one might ask. There are trade-offs to both.

1. Cells() is not known to intellisense. If you type a period after a word, if available, you get a dropdown list to choose from. That is if Tools > Options >Editor > Auto List Member, is checked as is the default.

2. Range() allows intellisense to work if option is set. You may have to use string concatenation as Alan did to build your range's input values. Avoid doing lots of string concatenation. That is a slow process. For a few concatenations, the slower speed is not noticed by humans.

3. Cells() lets you use number or index values for rows, the 1st input, and columns, the 2nd input, as you can see while typing it. Unknown to some is that you can also use the column letter. e.g.
Code: 
Sub Demo()
  Dim ws As Worksheet, r As Range
  For Each ws In ThisWorkbook.Worksheets
    Set r = ws.Cells(Rows.Count, "F").End(xlUp)
    If r.Row > 2 Then
      Debug.Print r.Address(external:=True)
      Else
    End If
  Next ws
End Sub
Click to expand...

After seeing this, I should strongly consider tweaking my code! Thanks!
 
For post #10, you should ask yourself, what is my goal?

If you remove the "> 2" then your code is saying: IF ANUMBER Then. This makes no sense so it will error.

One normally does not add an equality operator for True/False (boolean) comparisons as True will always be the True for an IF. e.g.
Code: 
Sub DemoTF()
  Dim tf As Boolean
  MsgBox tf, , "tf with no assignment, always False"
  MsgBox tf = False, , "tf=False is True since tf was False by default."
  tf = 1 = 1
  If tf Then MsgBox tf, , "tf = 1 = 1, is True since 1=1."
End Sub
Howsoever, there is nothing wrong using the equality for boolean variables, e.g.: If tf=True then...

The row being greater than 2 probably means what post #5 guessed. Let's say one has rows 1 and 2 with titles. Then one needs to check that data is there or else one might overwrite blank title values where data should go below.

Normally, I would use title rows in only row 1. I would then check with the End Up method to find the next non-blank value from bottom up. When found, I add one to a .Row return for the range. My preference is to assign a Range object to the find and then use .Offset(1) to offset one row below. In other words, I want to write to the next empty row in a column from bottom up. Why go bottom up and not End Down? That would stop at the last value before an empty cell. Sometimes, data will follow which is likely not what you wanted. Or worse, End Down could return the last row (1,048,576) if there is no data from the End Down location which is almost never what one wants.

Here is a case where End Down is safe. You built the data. A1 and A2 always exist and no blank values are allowed in a row as these are required values for an ID. I can then safely use End Down from A1 and Offset one row to get my next empty cell to add an ID value.

Here is a scenario. I find with my End Up Offset that row 2 is the next empty row. But, is it really? If the row is 2, then it might have stopped at row 1 whether it was empty or not. In that case, I use Offset(-1) for that range to check if row 1 was empty. Normally, that would be fine though as row one is most always used for column titles, fieldnames in database terminology.

All this End Up/Down and such may be confusing. Just do it manually or record a macro when you do it manually to see what is happening. e.g Press Shift+End in A1. Release and then press the Down arrow key.
 
Last edited:
upload_2017-11-2_18-0-4.png upload_2017-11-2_17-41-57.png
Kenneth Hobson said:
For post #10, you should ask yourself, what is my goal?

If you remove the "> 2" then your code is saying: IF ANUMBER Then. This makes no sense so it will error.

One normally does not add an equality operator for True/False (boolean) comparisons as True will always be the True for an IF. e.g.
Code: 
Sub DemoTF()
  Dim tf As Boolean
  MsgBox tf, , "tf with no assignment, always False"
  MsgBox tf = False, , "tf=False is True since tf was False by default."
  tf = 1 = 1
  If tf Then MsgBox tf, , "tf = 1 = 1, is True since 1=1."
End Sub
Howsoever, there is nothing wrong using the equality for boolean variables, e.g.: If tf=True then...

The row being greater than 2 probably means what post #5 guessed. Let's say one has rows 1 and 2 with titles. Then one needs to check that data is there or else one might overwrite blank title values where data should go below.

Normally, I would use title rows in only row 1. I would then check with the End Up method to find the next non-blank value from bottom up. When found, I add one to a .Row return for the range. My preference is to assign a Range object to the find and then use .Offset(1) to offset one row below. In other words, I want to write to the next empty row in a column from bottom up. Why go bottom up and not End Down? That would stop at the last value before an empty cell. Sometimes, data will follow which is likely not what you wanted. Or worse, End Down could return the last row (1,048,576) if there is no data from the End Down location which is almost never what one wants.

Here is a case where End Down is safe. You built the data. A1 and A2 always exist and no blank values are allowed in a row as these are required values for an ID. I can then safely use End Down from A1 and Offset one row to get my next empty cell to add an ID value.

Here is a scenario. I find with my End Up Offset that row 2 is the next empty row. But, is it really? If the row is 2, then it might have stopped at row 1 whether it was empty or not. In that case, I use Offset(-1) for that range to check if row 1 was empty. Normally, that would be fine though as row one is most always used for column titles, fieldnames in database terminology.

All this End Up/Down and such may be confusing. Just do it manually or record a macro when you do it manually to see what is happening. e.g Press Shift+End in A1. Release and then press the Down arrow key.
Click to expand...

Here's what I'm trying to accomplish:

I have an Excel workbook with about two dozen tabs. Each tab represents a salesman. Every week each salesman sends me their sales report. I use the first two rows of each tab for headers. Row 1 = name, Row 2 = column headers.

upload_2017-11-2_18-0-4.png

Note the left column is over 4600 deep. I append weekly to the bottom of each salesman's tab as they give me their new data. To make the data look clean and neat, I use a VBA that resets the font, font color, right/left/center justifies the columns, etc, because the salesmen send their info in as a jumbled mess. The VBA does its job; however, as I continually add more data each week, the VBA runs progressively slower. I am looking to speed up the process by making the VBA code as efficient as possible. What I need help with is understanding the code in post #1 as to exactly what it is doing. E.g. does it start at Row 1,048,576 and work its way up until it finds data to modify? OR, does it know to start in Row 3 (since I'm using the first two rows for name and column headers) and work its way down until it finds more than two or three blank lines (just in case I want to use a blank row for commenting half way through the data) and stop?
 
Right.

If column 6(F) has data in row 3 (F3) or further down the column, then it found that location. This should tell you it is in the last data value in column F since the row number is more than 2 since you have 2 rows for column headers.

End Up method is normally used in case there are blank cells. I like to do that for column A as it is usually a required field/column value.

Going by column F, seems odd to me. I guess there might be a reason.

If one looked at that row, that might not be the last row with data. e.g. A5="Ken" and F4=15. All rows below each are blank. Your code would find row to be 4 since it is looking from the last row in the sheet up to the first cell with a value or F1. If we were to write a value into the row below, that might not be what we really wanted. You get out of sync in other words.
 
upload_2017-11-6_14-12-59.png
So, the code on post #1 says start at Row 3 and work your way down until you hit two rows without data before stopping, correct?

Thank you for you help!
 

Attachments

  • upload_2017-11-6_14-12-33.png
    upload_2017-11-6_14-12-33.png
    4.2 KB · Views: 4
No.

I don't know what it does in the True part of the IF(). Let's call it, do stuff.

Let's look at some examples.

1. ws.[F3] is last cell in ws's column F with a value of say, "whatever".
a. IF() is True so do stuff. 3 > 2 = True

2. All cells in ws's column F are empty or = "".
a. IF() is False so goto Next worksheet. 1 > 2 = False

3. ws.[F2] is last cell with a value.
a. IF() is False so goto Next worksheet. 2 > 2 = False

4. ws.[F123] is last cell with a value.
a. IF() is True, do stuff. 123 > 2 = True
 
Kenneth Hobson said:
No.

I don't know what it does in the True part of the IF(). Let's call it, do stuff.

Let's look at some examples.

1. ws.[F3] is last cell in ws's column F with a value of say, "whatever".
a. IF() is True so do stuff. 3 > 2 = True

2. All cells in ws's column F are empty or = "".
a. IF() is False so goto Next worksheet. 1 > 2 = False

3. ws.[F2] is last cell with a value.
a. IF() is False so goto Next worksheet. 2 > 2 = False

4. ws.[F123] is last cell with a value.
a. IF() is True, do stuff. 123 > 2 = True
Click to expand...


You are correct. The TRUE part of the IF() does stuff.


I misspoke in post #15.
"So, the code on post #1 says start at Row 3 (COLUMN 6 which is also known as Column F) and work your way down until you hit two rows without data before stopping, correct?"

Thank you for the examples. More on those next time.
 
Kenneth Hobson said:
No.

I don't know what it does in the True part of the IF(). Let's call it, do stuff.

Let's look at some examples.

1. ws.[F3] is last cell in ws's column F with a value of say, "whatever".
a. IF() is True so do stuff. 3 > 2 = True

2. All cells in ws's column F are empty or = "".
a. IF() is False so goto Next worksheet. 1 > 2 = False

3. ws.[F2] is last cell with a value.
a. IF() is False so goto Next worksheet. 2 > 2 = False

4. ws.[F123] is last cell with a value.
a. IF() is True, do stuff. 123 > 2 = True
Click to expand...


Let's look at some examples.

I’m assuming the > 2 came from
IF ws.Cells(Rows.Count, 6.End(xlUp.Row > 2 Then
Correct?

1. ws.[F3] is last cell in ws's column F with a value of say, "whatever".
a. IF() is True so do stuff. 3 > 2 = True

(The red 3s came from Column F, Row 3, correct?)


2. All cells in ws's column F are empty or = "".
a. IF() is False so go to Next worksheet. 1 > 2 = False

(Where did the red 1 come from?)



3. ws.[F2] is last cell with a value.
a. IF() is False so goto Next worksheet. 2 > 2 = False

(The red 2 came from the F2, correct?)



4. ws.[F123] is last cell with a value.
a. IF() is True, do stuff. 123 > 2 = True

(I think seeing the other examples, helped me understand this one.)
So, for this example, we're telling it to start looking at F123, we're not defining a range, correct?
 
I think you are on your way. To set a range, which is an object, we use the word, SET.

e.g.
Code: 
Dim r as range, ws as worksheet
Set ws = Worksheets("Sheet1")
set r = ws.[F123]
MsgBox r.Value
MsgBox r.Row
MsgBox r.Address
'etc.
(4) was just to show, what if, End Up from last cell in F column found first value from bottom up to be in F123.
 
You must log in or register to reply here.
Back
Top