Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

VBA EXCEL, Getting The Row Number Programatically

How can I retrieve programatically the row number of the active cell? I am using VBA withing VB.

I need to know this so that I can programatically select a range of rows.

Thanks in advance

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Number Of Strings In A Resource File Programatically?
Hello everybody, is there a way to programatically know how many strings are in a resource file in your own program programatically? Currently I just look at the number of strings directly in the resource file.

Programatically Set Excel OPTIONS
I would like to programatically turn off 2 global Excel "Options"


un-select "Extend data range formats and formulas"

And in:
Tools->Options->Error Checking

un-select "Inconsistent formula in region"

I was able to set the global options Display Gridlines and Display Zeros thus:

xlAp.ActiveWindow.DisplayGridlines = False ' supress gridlines display
xlAp.ActiveWindow.DisplayZeros = False ' supress zero display
but cannot figure out how to set these other two.

Anyone know how?

Excel Formula Programatically?
I want to insert programatically an IF... formula in an Excel cell. When I try to use the Formula property, I always get the "Application Defined or Object Defined Error". Does somebody know, how could I resolve it?
Thanx in advance.

Programatically Remove Links Within Word To Excel.
I have a word document that has content linked to some
cells in excel. by that i mean, i copied from excel and
did a paste special with links. I can manually break these
links in word if i go to edit -> links and break all the
links. Is there a way of doing this through a macro. I
tried recording the link breaking procedure but nothing
showed up in the macro. The word vba help has an example
of where it shows how to break links but that seems to
work only for objects and not text.

thanks in advance

Programatically Remove Links Within Word To Excel.
I'm opening XLS and CSV files in VB for batch processing. One of the files has links to another XLS file, and it prompts me with a links questions every time I run the batch.

I cannot have an admin sitting there to answer the question, so how do I surpress the message or break the link?

I've tried to break the link, but that requires opening the files first, and that gives me the message prompt.

I've already tried this.

xlApp.DisplayAlerts = False

Any ideas?

Thanks in advance!

Importing Excel Worksheets Into Access Tables Programatically

I'm trying to import excel worksheets into existing Access tables using VB6
(not Access' or Excel's VBA). I have used ADO to connect to Excel
successfully, as well as to connect to the Access DB. What I want to do is
take the individual sheets of the XL workbook and import them into the
matched-format tables in Access.

There is a wealth of info on doing this in Access' VBA (which I can't use
for this project), and doing it manually is pretty easy as well (which is
not the point!), but there must be a way to do it in VB!

I'm thinking that a recordset containing the contents of a sheet should be
easy to read into the corresponding Access table, but I can't find an
example of this approach anywhere. Does anyone have an idea how I might

Thanks in advance! Have a good weekend!


Here's the function to gain entry into the Excel workbook (it works):

Private Function GetExcelConnection(ByVal Path As String, _
Optional ByVal Headers As Boolean = True) As Connection
Dim strConn As String
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""Excel 8.0;HDR=" & _
IIf(Headers, "Yes", "No") & """"
objConn.Open strConn
Set GetExcelConnection = objConn

End Function

And here's the code that cracks into the Access DB (just populating 6
comboboxes for now, it also works):

Private Sub LoadMyCBOs(MyCBO As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim countit As Integer
Dim oCBOControl As ComboBox 'new System.Windows.Forms.Control.
Dim cboStringName As String
Dim dbPathTo As String

dbPathTo = "C:projectsfor_alPPE_BUILDERvbcontract_builde r.mdb"

Dim cString As String
cString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & dbPathTo &

Set cn = New ADODB.Connection
cn.Open cString
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM PPE_CATEGORY", cn, adOpenKeyset, adLockReadOnly
If rs.RecordCount > 0 Then
Do While rs.EOF = False
cboCategory1.AddItem rs!ContractCategory
cboCategory2.AddItem rs!ContractCategory
cboCategory3.AddItem rs!ContractCategory
cboCategory4.AddItem rs!ContractCategory
cboCategory5.AddItem rs!ContractCategory
cboCategory6.AddItem rs!ContractCategory
End If
Set rs = Nothing
Set cn = Nothing

End Sub

Convert To Number The Number Stored As Text In Excel
can anyone help me on how to convert the number stored as text in excel when i exported my recordset in excel

this is how i exported my recordset
objExcel.Cells(2, 1).CopyFromRecordset ADODBRecordset

Making A Number Show Up In Excel As A Negative Number (value)
Ok I thought I had fixed my problem but I just made it look like it was right LOL

If arrRecords(n).string7 = 0 Then
      xlWorkSheet.Cells(n + 1, 6).NumberFormat = "0.00"
      arrRecords(n).string7 = 0#
      xlWorkSheet.Cells(n + 1, 6).NumberFormat = "-###,###,##0.00"
      xlWorkSheet.Cells(n + 1, 6).Value = arrRecords(n).string7
    End If

Ok when it writes to excel it looks like it is a negative number -100
However, when I click on the cell the value is 100.

So really is this part of my code is useless???
Code:xlWorkSheet.Cells(n + 1, 6).NumberFormat = "-###,###,##0.00"

how do i get the value to be a negative?


Edited by - epatterson78 on 12/8/2003 11:36:52 AM

Number Of Rows In Excel
What's the code to get the number of rows I have on an excel spreadsheet?
Or more exactly, what I need is for example something that returns A23, 23 being my last row

Re-number Excel Worksheets
Is there a way to re-number the worksheets in VBA? I am using a sub that hides each of my worksheets, except for the last one, but it does this by adding 1 to an integer variable, and then hiding sheet(i). Unfortunately, I have removed quite a few sheets, so my worksheet numbers are no longer in consecutive order. For instance, when I look at the project explorer, it looks like this:

Sheet1(Name of Sheet 1)
Sheet10(Name of Sheet 10)
Sheet2(Name of Sheet 2)
Sheet5(Name of Sheet 5), etc.

You see that Sheet3 and Sheet4 are no longer there, so when I run the sub that I mentioned above, I get a "out of range" error, because it gets as far as Sheet2, then it adds one, so that it can hide Sheet3, but Sheet3 is no longer there. Is there any way to re-number them, so that they will run in consecutive order again?

I even tried creating a new workbook and copying all of the sheets (which turned out to be a giant pain in the ***, because it screwed up all of my references, cell names, etc.) and each sheet maintained the sheet number that it had in the old file.

Excel - Extracting Number

I have an excel spreadsheet with a column that has a two letter abbrevation and then a number such as "SS123" I am looking just to pull the 123 out of the field in excel. I attempted to use the LEFT and RIGHT functions, however the numbers are different lengths (SS123, SS12345) so it does not work. Is there another way to do this in excel? Please advise.

Thanks in advance,


Excel VBA-Number Format If...
I have 4 nos. in validations (10,12, 15.2, 16) for a cell in excel sheet. I want to format the cell such that 15.2 if selected will display 15.2 but if any other is selected will not display decimal (say, should not display 10.0 if 10 is selected).
I put code in VBA as:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value = 15.2 Then
Range("A1").Value = 15.2
Else: Range("A1").Value = Range("A1").Value
End If
End Sub

This works fine, but...
the undo button is inactive, that means if want to undo the input, I cannot.
For all the things in the 'Worksheet_SelectionChange' this happens (the Undo button cannot be clicked)
Please explain why and provide a solution.

Get Last Non-empty Row Number In Excel
Hi all.
I'm trying to get the number of records that I exported from excel to a recordset. I have used .RecordCount attribute of the recordset and it gave me the number of rows successfully exported.
However, if i try to edit the excel file that I want to export, by deleting the contents of the cells by highlighting them and pressing delete, the number of records that will given through the .RecordCount attribute is still the original one.
But if i delete the rows by highlighting the rows themselves and clicking the Delete Rows command, the correct record count is given.
My problem is that, I protected the worksheet so there's only a limited action that can be done to the worksheet and deleting rows is not one of them.
How will I get the correct total number of records then?

Thanks all for always addressing my concerns...

Excel - Number With Prefix 0
I would like to enter number as text in Excel without using single quotes. Any help on this pls?

To be very clear, I just need something equivalent to Excel --> Format --> Cell --> Category --> Text (Select Text from list)


I currently have
xlSheet.Cells(1, 1).Value = ("01012004")

I don't want like this
xlSheet.Cells(1, 1).Value = ("'01012004") 'Single quote before 0


Excel -- Number Of Rows
Hey Folks,

I got a question that I should be able to answer myself, but I am having trouble doing so. How do I determine the number of rows that are populated in an excel sheet? I need to cycle through all the records and perform some formatting, but am having a problem determine the number of populated rows. Any ideas?

Codito Ergo Sum...
Erich Karch :
"A government is a body of people, usually notably ungoverned."

Excel Number Format
I have been working for a number of hours on Excel user defined format code in VB without any luck. What I am trying to do is to get a number format as follows:


Variable1 would change base upon data in certain cells
 By using this format for example I could make "Data Field 1" in a cell appear in EXCEL to be "Length" if Variable1 = "length" yet the value of the cell would still be "Data Field 1"


How To Count The Excel Page Number??
Dear All,

I have a program that Export the Data to the Excel to generate a time table to all the students and lecturers with have assigned to different course and class. In order to print the time table i have to calculate the page number of the Worksheet!!

The How to count the Page Number?????

Text To Number In EXCEL Macro...
Hi all,

Should be simple but.... Cant do it!

I have a .csv file where all data for each field are "" enclosed, regardless of format. i.e. text, numbers, dates etc are ALL within double quotes. Cant change this as its a download from a monitoring bit of kit.

so, opening the .csv file in excel using file open I can select a date column, and format date no problems. HOWEVER......

opening the .csv file from a macro results in the numerical data being treated as 'text' by Excel. Hence selecting a column and formating Date has no effect, as its actually treated as text.

All I need to do is change the column from text to number, when opening via the macro.

It must be a simple thing I am missing....
heres the file as it comes from the instrument:


DataField CS - GA Mode Data Output
"Device ID","Date/Time","CH4","CO2","O2","Baro","Rel Pressure"
"HOOQM005","16/09/2004 13:58:43","0.0","1.8","18.5","1014.0","-0.26"

Heres the macro code:

Workbooks.OpenText Filename:="\CspWOODVOL1GROUPSWASTEENVIRMANGA2000.csv"
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Selection.Format = "DD:MM:YY" 'this has no effect!!

any ideas?


Outputing Number With Format To Excel?
From vba to an excel cell A1,
How can you output a number and it's format in one line of code?

How To Let My Excel Have Exact Columns Number?
Hello, Everyone:
My data sheet has A-R columns and they has different width. So, it show to W column. I need to move scoller to see the first column. Would somebody be kind to let me know how I can remove the spare columns and let it show exact A-R columns which I just want?
Thank you very much!

Validating Number Of Entries In Excel
I am currently trying to get a marco to recongise when 86 Rows have been written in an Excel workbook. I have currently created a Macro to insert a new row and copy the validation to the cells in the new row, But I am having problems getting the If statement to work properly as it keeps displaying a messagebox no matter how many cells have been entered, even if they are under 86.

Please Help.

Sub AddNewHouse()
' AddNewHouse Macro
' Adds New House to the table

Selection.Insert Shift:=xlDown
Selection.AutoFill Destination:=Range("A2:K3"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("J2:J3"), Type:=xlFillDefault

'Makes sure no more then 84 houses can be entred
If Rows("86:86").Text >= "" Then
MsgBox "No more Records can be inserted here!"
End If

End Sub

Number Of Columns In Excel Using Automation?
How to get the number of columns in excel using excel automation??


Total Number Of Columns In Excel
How do I find the total number of columns in excel using OLE automation?

I tried doing a search on these forums but found no results.


Retrieving Row Number From Excel On Match
hello all,
I have the following code:

Sub Find_Matches()
Dim Match As Variant, CompareRange As Variant, x As Variant, y As Variant
'Set CompareRange equal to the range to which you will
'compare the selection.
'Set CompareRange = Range("C1:C10")
'Note: If the compare range is located on another workbook
'or worksheet, use the following syntax.
'Set CompareRange = Worksheets("Sheet2").Range("C1:C10")
Set CompareRange = Range("c1:c10")
'Loop through each cell in the selection and compare it to
'each cell in CompareRange.
For Each x In Selection
For Each y In CompareRange
If x = y Then
Match = CompareRange.Rows:
x.Offset(0, 1) = Match
Match = ""
End If

My problem is that when it gets a match, it always returns 1 as row number.
Here is my output with columnA being my Selection and columnB holding the

row columnA columnB columnC
1 1 1 1
2 2 1 4
3 3 6
4 4 1 1
5 5 0
6 6 1 a
7 7 1
8 8 1 2

Any idea how I can return actual row number?
Thanks very much!

Store Number As Text In Excel Using VB6
I manage to export recordset to Excel using VB6. Now I'm faced with a problem where Excel treats text fields as numeric. Meaning, if the original value is "0123", the exported value would become "123". What I need is to be able to export the value as is ("0123" not "123").

I tried .Cells(1, 1).NumberFormat = "0000" but it changes only the format not the actual value.

...Sure I'm missing something here. Can anybody help?

How To Know Th Number Of Rows In An Excel File?

I use an excel file to store my string data, how can I know number of rows
(lines) in my worksheet? (that have a string in them, ofcourse...)


Excel Macro Print Number
I have one excel worksheet. C9 is my LOG Number field.

Is it possible to get a Macro to generate a different LOG number each time the worksheet is printed? eg. sheet 1 lognumber 0001, second time it prints lognumber will be 0002 etc.

My one worksheet will be printed multiple times, i need a new log number on each worksheet every time it prints - log number goes into C9?

Any help would be great.

Stripping Spaces From A Number In Excel ...
I've got a column of numbers.
Each number is 19 digits long, and there are NO leading zeros.

Unfortunately, these numbers have intermittent spaces in the middle of them which I need to strip out.

e.g. 292 393 67675363 98769

I tried using the INSTR function, but that was giving me some weird results.

This is pretty urgent, so any input would be greatly appreciated.



p.s. I have posted in the VBA forum, but I need an answer pretty quick and no-one is in there

Assign Sequential Number To Excel
In my vb project, each time when the start_up form(main form)load, I have to generate a sequential number(say, seq_number which start from 100) , and put that number in excel sheet with other data that I might also want to save to excel

how to do this

Get Number Of Page Breaks In Excel

I'm trying to findout the number of Partial Horizontal Page Breaks.

I've found the following in the Help, but it doesn't seem to work: -

For Each pb in Worksheets(1).HPageBreaks
If pb.Extent = xlPageBreakFull Then
cFull = cFull + 1
cPartial = cPartial + 1
End If
MsgBox cFull & " full-screen page breaks, " & cPartial & _
" print-area page breaks"

The first question I have is is 'pb' declared as HPageBreak?

I've tried the code, but it doesn't seem to loop

Can anyone point me in the right direction?


Microsoft Excel Number Problem

When I enter any value that has ".19" in it, Excel changes the value to [value]".69". This happens even before the change event on that cell.

The strange thing is that it only happens on that one pc. If I use the same excel spreadsheet on another pc, this does not happen. I have checked the regional settings and the two pc are identical. Does anyone have any other ideas for me?

Excel - Number Format, Urgent
this is a part of my code

If rsemp.Fields("ac").Value <> "" Then
MsgBox (rsemp.Fields("hdfc").Value)
objWrkSht.Cells(i * 24 + 11, 2) = " " & Str(rsemp.Fields("ac").Value)
End If

the problem is msgbox gives the number in ordinary number format 010120001594.. but when displayed in excel it takes the exp form 1.012e+11.. why??

i need in number format only.. how to?? pls help me..

Excel Number To Text Conversion
i have cells that contain numbers but i want to convert them into text. anybody had any experience with this? thanks.

How To Get Number Of Worksheets In A Excel Workbook
Does anyone know how to get the total number of excel worksheets in a workbook?

How Do I Set The Number Of Copies To Print In Excel?
Using VB, I am printing out Excel sheets using the following code:


That works fine... but how do I set the number of copies for Excel toprint?


Excel: Converting Column Number To Name
I have a problem I just can get figured out (maybe because it's so late).

I need to find a way to convert a column number to the character form.
Ok, I will give an example, 1 -&gt; A, 2 -&gt; B, 52 -&gt; AZ, ... you get the point. So I came up with this brilliant idea (not really) to treat it in about the same way you would treat conversion from binary to hex, but using a 26-based-like system in stead of a 16-based. At first, all look good, but after some testing, I got some errors with multiples of 26 (not including 26 itself). I think the problem is that my system does not has a zero equvalent. After AZ must come BA, not B0 &lt;- B-ZERO.
Any suggestions

public Function XLCol(byval iCol as Long) as string

Dim lvl as Integer
Dim r as Long
Dim cpos as string
Dim strABC as string


lvl = 0

Do Until 26 ^ (lvl + 1) &gt; iCol
r = iCol Mod (26 ^ (lvl + 1))
cpos = mid(strABC, r, 1) & cpos
iCol = iCol - r
lvl = lvl + 1

cpos = mid(strABC, iCol / (26 ^ lvl), 1) & cpos

XLCol = cpos

End Function

Tom Cannaerts

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

Limit The Number Of Rows Returned To Excel
Dear Friends,

I'm developing a macro in Excel 2003, and I can't find out how I limit the number of rows returned to Excel from the database.
For example, I want to search the first log record in a database with the date of today.

So, I could write a SQL like this

Select table.field1, table.field2, ...
from table
where > today
order by
limit 1 rows ( ???? what do I write here ???? )

My script looks like this:

SQL_string = " select, a.value, a.action_dt " & _
" from sv_audit a " & _
" where a.action_dt >= " & end_date & _
" order by a.action_dt asc "
--- what do I put here ---

With Sheets(ws_temp).QueryTables.Add(Connection:=connect_string, _
Destination:=Worksheets(ws_temp).Cells(l_cnt, 10), _
.FieldNames = False
.Refresh BackgroundQuery:=False
End With


Extracting The Number Of E-mails To An Excel Sheet
Hi There,

Good day, does anyone know how to

1) Get the number of of e-mails from each individual folder and subfolders of outlook.
2) And after that extracting this data to an excel spreadsheet.

Thanks for your assistance.

Excel Formulae To Output Text And Number

Is there a way in Excel to use a formula (not VBA) to display text in a cell that is part made up of a String, part reference to a numeric value in another cell?

I am trying the following, but just getting the #VALUE! error returned:

="Change between " + YEAR(B9) + " and " + YEAR(C9)

Any help would be greatly appreciated.



Find Excel File Number Of Rows
How can i find the valid number of rows that i have loaded in a VB project in order to apply it as upper bound in a matrix?

Private Sub Command1_Click()
Dim phonesLabels(1 To 161)
Dim i As Integer
For i = 1 To 161
' Fill the array with seven values from column B of
' the worksheet.
'arrPrices(i) = wkbObj.Worksheets(1).Range("B" & i + 1).Value
phonesLabels(i) = wkbObj.Worksheets(1).Range("A" & i + 1).Value
Next i

Get Number Of Rows With Data In A Range In Excel
My excel sheet looks like this
row 1 to row 150 have data and formating
row 151 to row 1600 have formating only

I need to find out the last row with DATA ONLY in Excel, however when I use the code below the count shows 1600 - all rows that have cell formating (backcolor etc) applied even if those cells have no data in them.

VB Code:

I also tried these with same problem:

VB Code:
ws.Cells.SpecialCells(xlCellTypeLastCell).row  xlApp.ActiveCell.SpecialCells(xlLastCell).Address(RowAbsolute:=False)  ws.UsedRange.Rows.Count  ActiveCell.SpecialCells(xlLastCell).row

How can I get the count of all the rows with data only.

Excel Returning Wrong Number Of Sheets
I'm using code to determine the number of sheets in an excel file, so I can scroll through it and retrieve information.

However, when I pull back XLwb.Sheets.Count, I get back 10.

The Excel worksheet contains only 8 sheets (its being generated by another application). In Excel, I've tried show hidden, and there is one hidden column.

At run time, if I try and loop through all 10 sheets, when it hits number 10, I get an ActiveX automation error, saying it is out of range.

On other Excel sheets I've tried, its actually returning the correct value, but not others, so I can't even manually adjust it by -1.

Any one see this before?

How To Determine The Number Of Rows In An Excel File
In my VB application I am opening Excel files, reading through the data one row at a time, and doing calculations for each row.
Do While CurrCell <> ""

'*** read each row and perform calculations ***


Each file has a different number of rows. What I would like to do is first determine how many rows of data there are in a particular file when I first open it so I can show (with a ProgressBar or label) a "percent completed". How can I get a count of the number of rows in a file based on the first column where the first row is a title and the remaining rows are dates?

How Do I Get The Number Of Rows In An Excel Worksheet Column

I've have crete a app to read Excel worksheets into an array but I can't find a function/property to return a rowcount of a column any ideas?????

The msdn help says the following...

Tip: Use the CurrentRegion property to return the number of rows in an Excel column. You can then use this number to specify the upper bound of the array's first dimension.

But I cant get it to work
Heres my code.........

Dim xlsWorkBook As Excel.Workbook
Dim xlsWorkSheet As Excel.Worksheet
Dim xlsRange as Excel.Range
Dim iCount As Long

Set xlsWorkBook = GetObject("C:vbtest.xls")
Set xlsWorkSheet = xlsWorkBook.Worksheets(1)

Set rCol = xlsWorkSheet.Columns(1)
xlsRange = rCol.CurrentRegion

Debug.Print xlsRange.???? 'what has the row count it


Please help!

Total Page Number In Excel Sheets
Hello folks!

Does anyone have a clue how I can retrieve the total page number required for printing out an Excel sheet in VB/VBA ?

I've got an Excel sheet, and I want to print out the current and total page number in the footer, but I want to print nothing in the footer, if the total page number is 1 ... so I have to retrieve this number before printing the sheet ...

The only thing I have is the printing area I want to print out ...

Thanks for any help


Find Number Of Rows In Excel File
I am importing an Excel file, processing it and writing an Access DB.  I open the file with the following code:

 Dim wBook As Excel.Workbook
 Set wBook = GetObject(filename)

and am able to access the cells I need.  However, I can not detect the end of the spreadsheet.  How can I determine the number of rows in the spreadsheet?


Referring To A Range In Excel Using A Variable Number??
I am currently referring to a specific range in excel using Visual Basic 6.0 as follows:
This works fine.
Although I am trying to refer to cells using a variable number within a loop, so I can access a specific cell using a set up variable called num, how would i do this??
I have tried .range(num, 1), though i think this might be for VBA, as it is not working. I am using the variable to refer to a row number btw!

Any help would be great, thanks!

Edited by - ser1alkiller on 12/23/2003 4:35:37 AM

Working With Excel Number Field To Text
I need to take an Excel number field and convert it to text (use a legal purchase agreement)




One Million One Hunred Twenty Five Thousand two Hunred Thirty Five

Does anyone know of a function to convert numbers in to text like this???


Page Number Function In Excel 97 **urgent**
is there a function in excel that would allow me to insert a page number into a cell. The way the spreadsheet is formatted means that they want this in a cell rather than in the page header or footer.

Any help would be most appreciated.

Copyright 2005-08, All rights reserved