CODEWITHSUNDEEP
excelvbaworksheet-function
user10696147
user10696147

Reputation: 53

Range().Cells().Row

I was troubled by the following code

Dim xTRrow as Integer
Dim xTitle as String
Dim xSht as Worksheet
xTRrow = xSht.Range(xTitle).Cells(1).Row

I wonder what the last line does. Especially, the .Row. In case as such, how do can I run the VBA code line by line to find out what a specific line of code does? I guess something is off with the code. I've tried to display xTRrow which should be an integer. But nothing jumps on the screen. I wonder what the last option .Row does.

Upvotes: 2

Views: 8276

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Row Trouble

Description

The variable 'xTRrow' is equal to the 'row' of the '1'st 'cell' of the 'range 'xTitle' in the worksheet 'xSht'. You can make it work by defining the missing data.

The Code

Option Explicit

Sub RowTrouble()

  Dim xSht As Worksheet  ' A Worksheet
  Dim xTitle As String   ' A Range Address (or a Named Range)
  Dim xTRrow As Long     ' A Row - rows are best declared as Long.

'  ' Your code (not yet working)
'  xTRrow = xSht.Range(xTitle).Cells(1).Row

  ' Define the range
  xTitle = "A1:D1"

  ' Create a reference to the worksheet with the name "Sheet1"
  Set xSht = ThisWorkbook.Worksheets("Sheet1")

  ' Your code working
  xTRrow = xSht.Range(xTitle).Cells(1).Row

  ' To display the result in the immediate window
  Debug.Print "The first row in my workbook's range (" & xTitle _
      & ") is equal to " & xTRrow & "."

  ' To display the result in a message box:
  MsgBox "The first row in my workbook's range (" & xTitle _
      & ") is equal to " & xTRrow & "."

  ' To display the result in the sheet:
  xSht.Range("A1") = "The first row in my workbook's range (" & xTitle _
      & ") is equal to " & xTRrow & "."

End Sub

How

Open a new worksheet. Go to Visual Basic Editor and insert a new module into the worksheet. Copy/paste the example into it. Open the immediate window to be able to see the result. Run the code.

Line by Line

Too loop through the code line by line under Debug select Step Into or just use 'F8':

enter image description here

Upvotes: 0

cybernetic.nomad
cybernetic.nomad

Reputation: 6388

In addition to using F8 to step through the code, you can use debug.print to display the values of relevant variables before and after the given line. That said as you use VBA, you will be able to recognize what object and method is. Assuming the code works fine and all variables and objects were dimmed and set properly:

xSht.Range(xTitle).Cells(1).Row

break down as follows:

xSht : variable containing a sheet (which sheet we don't know as that part of your code is missing in your question)

xTitle: probably the name of a named range (which range we don't know as that part of your code is missing in your question)

Cells(1): Cell no; 1 of the above mentionned named range

Row: the row of the cell in question

So xTRrow should be the row number of the cell in question. (BTW, it really should be Dimmed as Long as Excel can have more rows than Integer allows for

Upvotes: 1

Related Questions