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




Determine Number Of Columns/rows In Excel Spreadsheet That Have Data?


Hello,
I want to get all the rows and columns in a Excel spreadsheet and put them into an MSHFlexGrid.

Is it possible to determine the number of columns/rows in the spreadsheet that has data in them to properly
size the MSHFlexgrid to match?
Otherwise, I have to make the MSHFlexgrid very large.

Thanks!
Regan




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Determine The Number Of Rows/Columns In Excel File [VB6]
Given an excel file with X number of rows each having Y columns.
I need a mechanism that will allow me select each Cell.

For example:
For ( j = 0; j < x ; j++)
For (k = 0; k < y; k++)
Do whatever needs to be done.

This will then go over each row and each column in each row.

Given that I am importing data I do know beforehand how many columns (Y) should be there, I just want to ensure there is actually the right amount and therefore I need a way to determine (Y) from the file itself and compare it with the # I am expecting.

For the rows it is a little different, I need to go down and loop through all rows in the file, how do I tell it when to stop?

Is there a totally different/better way to do this?
Something built into the Excel object maybe? (Excel.endrow or something?)

Code so far:

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(c: est.xls, True)

Thanks,

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.
Code:
Do While CurrCell <> ""

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

Loop

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?

Loop To Count The Number Of Populated Rows In An Excel Spreadsheet - Any Help Please?
Hi,

Please can you help. I have a spreadsheet containing a large set of data, and I want to be able to count the number of rows of data that I have using a VB script. ( I want to do this to reduce the amount of time that it takes to do calculations and vlookups etc).

The result that i want is a number that i can use again within a script, and i want to be able to specify which column i am using the loop in.

I had something like this before with which had some of the following code, but i am not sure how to write it again!

------------------------------------------

Dim mycounter as counter
mycounter.clear

Range Ax.select

if Ax = non-blank, go to Ax+1
Add 1 to mycounter

Loop until selected cell is blank

then display the contents of mycounter

-------------------------------------------

Can anyone help please??

Thanks in advance

James Skeggs

Sort 2 Columns In A Excel Spreadsheet Whose Data Is Got From A Msflexgrid In Vb
Is it possible to sort 2 columns in the excel spreadsheet before i export the data from the grid to an microsoft excel spreadsheet.If so,how?

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:
UsedRange.Rows.Count


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.

Count Number Of Used Rows In A Spreadsheet
I am trying to write a macro using VB in excel that will count the number of fields in a column on a pre-selected spreadsheet that have been used (have a value in them) and then copy just those cells onto a new spreadsheet.

I am a bit of a VB newbie, so any help would be greatly appreciated!!!!!!

How To Determine Total Number Of Rows With ADODB
Hello!

Like title says, how can i determine the total number of rows in Excel 2002!?
Im using an ADODB connection to a Oracle Database.

I cannot use the excel 97' way (see example below)
recArray = rs.GetRows
recCount = UBound(rs.GetRows, 2) + 1


Any suggestions?



Edit by Moderator:
Please post database questions, in the Database and Reporting forum.

Thank you.

Limit The Number Of Rows And Columns ?
I am trying to trim down the size of my workbook, and have discovered that if the cells around my form (print area) in the worksheet are coloured (ie grey) then they add to the overall size of my workbook considerably,
I want to keep a small border around the forms I have created because it looks much better, but dont want the user to be able to scroll down or right forever, which is what excel will do.

So i was wondering if there was a way to limit each worksheet to a set number of rows and columns ? this would help me reduce the size significantly

cheers

Gunslinger

Finding Number Of Rows And Columns
I am creating a web page builder specific to its parent program, one of the functions I would like it to do is automatically insert a table with dde links to an excel spread sheet.

I know how to insert the table, but I dont know how to find the number of rows and columns in the excel spread sheet. I would preferably like to have the user do this by clicking on the file name via the commondialog for ease of use, and perhaps using the OLE???

thanks for any help

Limit Total Number Of ROWS/COLUMNS
Hi,

I'm working on a worksheet created by someone else. It has lots of VBA code included in it. The sheet itself has been formatted so that it only goes down as far as 1999 rows. Then it just stops. the rows that should be after it, doesn't appear...no row headings (numbers) or anything...just a solid color. I need to extend this back to its original form as i now have a new requirement that requires a minimum of 10,000 rows.

I get the feeling that the rows are actually still there, and i just can't see them. can someone tell me how to get these rows to appear again. I have been through all the options i can find, and no luck. Its not done by selecting a row, right clicking and then clicking "Unhide" (because how can i select something that i can't see?)

Just to clarify... There are literally NO cells below the 1999 mark. I can't select empty cells, i cant see row names(numbers), nothing. Something else that might be significant is that when the sheet is at the top, and the scroll bar is all the way up, if i grab it and move it down, it moves down in VERY large chunks. The alt message that says what row your currently on moves around 130 rows every 5mm i move the bar down. The scroll bar is the normal size that it would be on a brand new sheet though (i.e. its not really really small like it would be if there were thousands of readings)...oh, and it never changes in size.

Please, can someone tell me how to undo this very annoying formatting that my predesessor lef me with.

Thanks for any help
Pile

Count Number Of Rows And Columns In A Page
hi,

i need to know the number of rows and columns before a pagebreak is reached, but i don't have any idea how to do it,

does someone has any suggestions?

thanks

DBGrid- How To Change The Number Of Rows And Columns?
I have a dbgrid that is unbound. Is there any way I can specify the number of rows and columns? or Do I need to have it bound to a database. Either way, please let me know. I should be able to edit the contents at run time.

Accessing No. Rows In Excel Spreadsheet From VB
I am writing a data import utility which takes data from an excel spreadsheet and loads them into a
database. When I count the no records in the spreadsheet it returns 60000+. This seems to be the
number of rows in the spreadsheet, but only 6500 have data in them. Is there a way of figuring out
how many rows with data are in the spreadsheet as this will reduce processing time from 60 mins to
5 mins.

Thanks
Shane

Copying Rows From One Excel Spreadsheet To Another
Hi all,
Never done this before at all and need some help - I have rows 1 to whatever, want Range(A1:M1) to be copied from one sheet to another. Every second row should do this - Below is what I currently have but I am having trouble with this line,
ActiveCell.FormulaR1C1 = "='Data-csv'!R" & "A" & intRow & ":" & "M" & intRow & "C" - I don't know how to write this line properly..Can you help me?

Hey, I actually was able to find an answer to the first question but I really need help copying a range of cells from one sheet to another and dynamically doing this for every second row in one step - This is what I have so far but it's not working - I can't figure out how tp get this line working at all: 'ActiveCell.FormulaR1C1 = "='Data-csv'!A" & intRow & ":M" & intRow & ""


Sub s()
'
' s Macro
' Macro recorded 5/5/2003
'
' Keyboard Shortcut: Ctrl+s
'

    Dim intRow As Integer
    Dim intRowCount As Integer
        
    intRowCount = ActiveSheet.UsedRange.Rows.Count ' Get the used range
     
        For intRow = 6 To intRowCount
            Range("A" & intRow & ":M" & intRow & "").Select
            'ActiveCell.FormulaR1C1 = "='Data-csv'!A" & intRow & ":M" & intRow & "" Can't get this line working at all
            Selection.Copy
            Range("A" & intRow & ":M" & intRow & "").Select
            ActiveSheet.Paste
            intRow = (intRow + 2)
        Next intRow
End Sub



Edited by - MajorDog on 5/5/2003 12:04:12 PM

Sorting Columns In An Excel Spreadsheet
Hello everybody,

I am having some trouble sorting excel spreadsheet columns from a VB application and was hoping somebody could help...

Here is what I am doing:

dim xlApp as Excel.Application
dim xlBook as Excel.Workbook
xlBook.Worksheets(1).Range("A1:C14").Select


and so i want to sort ascending on column C (with all the data in columns A and B still matching the data in C, hence the select of the whole thing), and the command that the macro recorder gave me was:

xlApp.Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

which I know may not be the same thing I need because it is VBA, not VB. I have tried a couple of different things (such as using "Cells" and specifying the entire range), but keep getting this error:

Run-time error '1004':

Method 'Range' of object '_Global' failed



Can anyone help me out and let me know what I am doing wrong, or shoot me a sort command that works in VB?

Thanx a bunch in advance!

ralph m.

Vb 6.0 - Spreadsheet Rows Has Hidden Data
Hello members,
I need help with my spreadsheet display problem. I generated my spreadsheet in using vb6.
Everything is working except that when I display the display (making it visible), some of the data in the rows hides underneath the row lines. I have to physically go into the spreadsheet to expand the rows to see the hidden data. Also, if I print the spreadsheet, the hidden part of the spreadsheet is cut off unless I mannually expand the rows. How can I programmatically expand the rows to fit each amount of records in the row. Currently, I have a module that is mannually calculating the rows but I may be doing it wrong.
Can anyone help please?
Also, I have attached the spreadsheet so that you can see my output.

Below is the code that calculates and formats the row presently:
Code:
   w = 0
    For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next
    
    rht = xlWksht.Range("A5").RowHeight

With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14))
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlTop
            .WrapText = True
            .Orientation = 0
            .MergeCells = True
            .RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size
End With


Below is my entire module:
Code:

    ii = 5
     w = 0
    For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next
    
    rht = xlWksht.Range("A5").RowHeight

    'Do Until M.qBW.EOF = True
     Do While Not M.qBW.EOF
        ii = ii + 2
        xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
        xlWksht.Cells(ii, 2).Value = M.qBW![Description]
        xlWksht.Cells(ii, 3).Value = ""
        xlWksht.Cells(ii, 4).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
        xlWksht.Cells(ii, 5).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]

        SrchCriteria = "[Name]= " & "'" & M.qBW![Personnel2] & "'"
        rsinPers.FindFirst SrchCriteria
        If rsinPers.NoMatch = False Then
           xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
        End If
        
        SrchCriteria = "[Name]= '" & M.qBW![Personnel3] & "'"
        rsinPers.FindFirst SrchCriteria
        If rsinPers.NoMatch = False Then
           xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
        End If
        
        SrchCriteria = "[Name]= '" & M.qBW![Personnel4] & "'"
        rsinPers.FindFirst SrchCriteria
        If rsinPers.NoMatch = False Then
           xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
        End If
        
        SrchCriteria = "[Name]= '" & M.qBW![Personnel5] & "'"
        rsinPers.FindFirst SrchCriteria
        If rsinPers.NoMatch = False Then
           xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
        End If
        
        SrchCriteria = "[Name]= '" & M.qBW![Personnel6] & "'"
        rsinPers.FindFirst SrchCriteria
        If rsinPers.NoMatch = False Then
           xlWksht.Cells(ii, 10).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hrs]
        End If
        
        xlWksht.Cells(ii, 11).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
        xlWksht.Cells(ii, 12).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
        xlWksht.Cells(ii, 13).Value = M.qBW![Start Date] & Chr(10) & M.qBW![Start Date]
        xlWksht.Cells(ii, 14).Value = M.qBW![End Date] & Chr(10) & M.qBW![End Date]
        
        If M.qBW![Comments] = "" Or IsNull(M.qBW![Comments]) Then
           mystr = "Comments:" & Chr(10) & "NO COMMENTS FOR THIS RECORD!"
        Else
           mystr = "Comments:" & "'" & xlApp.Clean(Trim(M.qBW![Comments]))
        End If
        
        Do
            Pos = InStr(Pos + 1, mystr, ":")
            If Not Pos = 0 Then
               If Mid(mystr, Pos - 5, 1) = "/" Then
                   mystr = Left(mystr, Pos - 11) & Chr(10) & Mid(mystr, Pos - 10, 10) & Chr(10) & Mid(mystr, Pos + 1)
                   Pos = Pos + 2
               End If
            End If
        Loop While Not Pos = 0
        
       
        xlWksht.Cells(ii + 1, 1).Value = "Comments:"
        xlWksht.Cells(ii + 1, 2).Value = Mid(mystr, 11)
        
        With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14))
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlTop
            .WrapText = True
            .Orientation = 0
            .MergeCells = True
            .RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size
        End With
         
        xlWksht.Columns("A:A").ColumnWidth = 9.15
         
        'Draw Underline after each record:
        '---------------------------------
        TStr = "A" & CStr(ii + 1) & ":N" & CStr(ii + 1)
        xlWksht.Range(TStr).Select
        If Not IsEmpty(Selection.Range("A1")) Then 'check if first cell is empty
           With xlWksht.Range(TStr).Borders(xlEdgeBottom)
           .LineStyle = xlDouble
           .Weight = xlThin
           .ColorIndex = xlAutomatic
           End With
        End If
        
        M.qBW.MoveNext
    Loop

Thanks.
GiftX

 

Macro: Copying Rows To Another Excel Spreadsheet?
Hi,

New to the board.

I am current writing some VB code for some Excel macros for work, but I have never learnt VB so I am picking it up as I go along.

I am trying to copy rows of a spreadsheet to other spreadsheets based on a cell condition.

I know the current code I have...

If Cells(x, 5).Value = "8" Then
Cells(x, 1).EntireRow.Copy
Sheets("Sheet1").Cells(a, 1).PasteSpecial
a = a + 1
End If

...works to copy rows to another sheet in the same spreadsheet, but how would I copy to Sheet1 of another spreadsheet, called "Test.xls", say?

Any help would be appreciated.

Cheers
Nick

Bypass Blank Rows In An Excel Spreadsheet
I'm sure I read the answer to this question, but I can't seem to find it.

What has to be done in a VB 6.0 program to bypass blank rows in an excel spradsheet.

Thanks

Geod

 

Hiding Columns/Rows In Excel
Hello, is there any way that I can hide columns&rows while having a sheet selected for data input?
Im using the "Application.DisplayFullScreen = True" command but have found no way to hide columns/rows and menu options.

Thank you.

Change Rows&&Columns In Excel
Is there a way to change the number of rows and columns in excel. For example my version of excel has 256 colums and 65536 rows. Can I for example change this into 256 Rows and 65536 Colums. With other words I just want to swap the rows for columns.

Selecting Rows/columns In Excel
Hi,

this is a very simpy question (and what bugs me I used to know the answer, but I have forgotten it and can't remember).

I want to create a comboBox in Excel and then display the contents of other cells dependend on what I have chosen in the comboBox.

Basically it would be a case where the cell displaying the result would have something like this:
=Ji
J being the column (constant)
i being depended on the result in the 'cell link' field.

J1 would contain the first information being chosen
J2 the second and so forth

Right now Excel doesn't recognize the 'i' as a variable and complains (how do I tell it that it is one?)

Thanks, PJ

Excel Rows Ansd Columns
Is there a way to detect the number of IN USE rows and columns in a spreadsheet?

Count Rows And Columns In Excel
Does anyone know how to programmatically know how many rows and columns are in a current Excel worksheet?

No L c
VB Developer

Pasting Data To New Rows In Columns
My spreadsheet takes data (a price and a calculation) and pastes it into a new row when either the price or the calculation changes (by .4 in this case). The problem I have is that when the price or the calculation changes by less than .4, it doesn't paste data (which is correct), but it "remembers" and when the price or calculation changes by .4 or more the next time, it "pastes" blanks to a row and then pastes the correct data to the next row.

So in essence, it skips a row and then pastes the data to the next row. This is messing up my ability to count which is the last row with data, as it doesn't count the blanks and so I may have 7000 rows with data, of which only 5000 have real data. Then the COUNT function thinks the last row with data is row 5000.

Here's my script for the for the pasting data section:


Quote:




Option Explicit

Public m_lLastPrice As Double
Public m_lLastCalc As Double
Public m_lStartingRow As Double
Const PRICEROW = 5
Const PRICECOL = 17
Const CALCROW = 6
Const CALCCOL = 17
Const RANGESTARTROW = 1
Const RANGEENDROW = 5000
Const RANGESTARTCOL = 2
Const RANGEENDCOL = 3

Public Sub Init()
On Error GoTo eh

m_lLastPrice = 0
m_lLastCalc = 0

m_lLastPrice = CDbl(Val("" & Me.Cells(PRICEROW, PRICECOL).Value))
m_lLastCalc = CDbl(Val("" & Me.Cells(CALCROW, CALCCOL).Value))
m_lStartingRow = 0

Me.Range(Me.Cells(RANGESTARTROW, RANGESTARTCOL), Me.Cells(RANGEENDROW, RANGEENDCOL)).Clear

MsgBox "Starting Price:[" & m_lLastPrice & "] Starting Calc:[" & m_lLastCalc & "]"

Exit Sub
eh:
m_lLastPrice = 0
m_lLastCalc = 0
End Sub

Private Sub Worksheet_Calculate()
On Error GoTo eh
Dim lPrice As Double
Dim lCalc As Double
Dim dPrice As Double
Dim dCalc As Double
Dim bDidUpdate As Boolean

bDidUpdate = False

dPrice = Me.Cells(PRICEROW, PRICECOL).Value
dCalc = Me.Cells(CALCROW, CALCCOL).Value

lPrice = CDbl(dPrice)
lCalc = CDbl(dCalc)

If ((CDbl(lPrice) - dPrice) <> 0) And ((CDbl(lCalc) - dPrice) <> 0) Then Exit Sub

If (Abs(m_lLastPrice - lPrice) >= 0.4) Then
bDidUpdate = True
m_lLastPrice = lPrice
Call InsertPoint(lPrice, lCalc)
End If

If (Abs(m_lLastCalc - lCalc) >= 0.4) Then
m_lLastCalc = lCalc
If Not bDidUpdate Then Call InsertPoint(lPrice, lCalc)
End If
m_lStartingRow = m_lStartingRow + 1
Exit Sub
eh:
MsgBox "[Sheet2.Worksheet_Calculate] Error:" & Err.Description
End Sub

Public Sub InsertPoint(ByVal p_lPrice As Double, ByVal p_lCalc As Double)
On Error GoTo eh

If m_lStartingRow < 0 Then m_lStartingRow = RANGESTARTROW

Me.Cells(m_lStartingRow, RANGESTARTCOL).Value = p_lPrice
Me.Cells(m_lStartingRow, RANGEENDCOL).Value = p_lCalc

m_lStartingRow = m_lStartingRow - 1



Exit Sub
eh:
MsgBox "[Sheet2.InsertPoint] Error:" & Err.Description
End Sub




Any suggestion on how I can get this to not paste blank rows would be greatly appreciated!

[Excel 2002] Dynimic Rows And Columns
I want to have in MS EXcel2002

an input:

NUmber of rows:
Number of colums:

Then i want to dynamicly generate these if filled in with a layout and formulas in them i define.

Is this possible and how?

To Count Rows And Columns In An Excel Document
hi all.......i would like to know how to write a programs to count the number of rows and columns of an excel document.......eg: .usedrange.rows.count...

thank for your help...."P

- VB AND EXCEL - Select Multiple Columns And Rows
Hello All,

I need to merge b5 to d5, e5:g5 (in excel-when in the process of crystal reports) in loop. i.e I want to merge every 3 colums in my excel starting "B" Column.


Code:
Do While Not rcl.EOF
startcolnoclient = 2 '' i.e 'B' column
xl.Application.Range(5, startcolnoclient & ":" & 5, startcolnoclient + 2).Select
- wrong code!!!!!!!
xl.Application.Selection.Merge
startcolnoclient = startcolnoclient + 3
rcl.MoveNext
Loop


Can you help me how to select multiple cells in number mode. I know how to select in alphabet mode(xl.Application.Range("b2:E5").Select).

Thanks

***Resolved*** Excel Columns And Rows Count
Hi All,

I would like to fined out how many columns and rows i have in my spread sheet in my excel.

I tried to get it with

wksheet.Columns.count

but it gave me all the columns and not only the one that are filled.

thank - Lin.

Comparing Values In Columns And Rows In Excel
Ok, heres what I need my excel macro to do:

I have two columns - in Column A there is a list of numbers 50-60 (from row 1-10) in Column B I have a list (from row1-3) of 3 numbers - 50,55,59

I need the code to compare the number in A & B (row by row) and if the numbers dont match then insert a Row in Column B until the numbers match (eg Both Column A row 5 and Column B row 5 should have the number 55 in it)

Im guessing an IF statement is required, but not sure how to best utilise it

Any advice would be much appreciated

Many Thanks

Changing Data Input From Rows To Columns
Newbie here. I have a macro in Excel that takes data from another program (Pcdmis v. 3.7) and inputs it into rows in Excel (v. 2003). I would like to make it input the data into columns instead of rows. Is this an easy fix, and if so, what code do I look for to change from rows to columns? I have no experience using Visual Basic.
Thank you...

Copy Values Of Big Excel Range (many Columns And Many Rows)
Hi all...
First, excuse me for my Malaysian English..

I'm doing this in Excel VBA:

WBOne.Sheets(1).Range("B10:FB510").Value = WBTwo.Sheets(1).Range("B10:FB510").Value

This code is running from my Addin (XLA).
Both workbooks opened as shared workbook, and both have the same
Sharing Properties (KeepChangeHistory = False)

The problem is: Excel VBA can make it at the first pass, and sometimes
it can make it at the second pass, when the values is swapped between the two workbooks.
Then, at the third pass, Excel seems to be chocked, it takes too long time and begin to be not responding anymore. (I'm using Excel XP in WinXP).
This code also did the same:

WBOne.Sheets(1).Range("B10:FB510").Copy WBTwo.Sheets(1).Range("B10:FB510")

Is that a bug?
Because it is too many COLUMNS and rows to be copied...
But Excel manage to do it at first and/or second pass.

Excel can make it many times if there is only ONE column (and very many rows).

Any help would be very appreciated.
Cheers.

Excel VBScript To Detect Insert Or Delete Rows/columns
I've created some VBScript routines that use MS Excels Worksheet_Change event to create a 'change log' (ie when somebody changes data in the worksheet it gets stored in another worksheet for future reference).

All seems to be working well, but I've hit a mental block when tryingto figure out whether rows (or columns) have been inserted or deleted.

Essentially I'm using Ranges and the sizes of ranges to determine when a row (or column) has been inserted or whether just a block of cells have been changed.

As I say this seems to work pretty well (although it does feel like a bit of a bodge).

Is there any way to easiy recognise whether a row (or column) has been inserted or deleted and whether it was an insertion or deletion?

Cheers,

FM

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
Thanks

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

Get Number Of Rows That Contain Data
I'm using a function that returns the number of rows that contain data in a worksheet:


Code:
Function LastRowInColumn(intCol As Integer) As Integer

On Error GoTo LRICError

Application.Volatile 'This will make sure that this function is called if anything is changed on the Worksheet

LastRowInColumn = Cells(Rows.Count, intCol).End(xlUp).Row

ExitFnxn:
Exit Function

'If there's an error in this function, then return an error to Excel

LRICError:
LastRowInColumn = CInt(CVErr(xlErrNA))
Resume ExitFnxn

End Function
The problem is that it works ONCE, but if I use the function more than once, it returns 2042 instead of the actual number of rows in my spreadsheet (50). I have no idea where this 2042 is coming from and I really don't know what to do...why would this work when the program is first ran, and not anymore? Any help is appreciated.

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

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

thanks

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!
Charlie

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

Thanks

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.

Thanks

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 table.date > today
order by table.date
limit 1 rows ( ???? what do I write here ???? )

My script looks like this:

SQL_string = " select a.id, 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), _
Sql:=SQL_string)
.FieldNames = False
.Refresh BackgroundQuery:=False
End With

Thanks,
Gerrit

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?

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

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

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

xlsWorkBook.Close
'''''''''''''''''''''''''''''''''''''''''''

Please help!
ft

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?

Thanks.

How To Get The Number Of Rows In An Excel Worksheet Extractly?
I want to know number of the rows (contains data, not blank row) in an Excel worksheet extractly. How can I do it in Visual Basic, not VBA? Thx a lot!

Adding Number Of Rows In A Data Grid
How do I get a data grid to add up the number of rows that are in the data grid

Data Combo Box - Number Of Rows To Show
Hello again,

My data combo box only has 2 values, male, female. When I click on the loaded combox box the drop down list contains my values, male and female but also has 6 more blanks. The number of rows to show in the dropdown is too many. It needs to be only 2.

How do I make VB do this?

Rich

Invalid Data In Excel Spreadsheet/count Group Of Items In Excel Sprdsh
Please help:
Problem#1:
I populated an excel spreasheet with data from access table, using CopyFromRecordset function.  The process was successful and all the fields displayed correctly in the spreadsheet, except the date field which displayed:"########".  What did I do wrong?

Problem#2:
What vb code can I used to count group of items in the spreadsheet.
Any insight will be very helpful.
Thanks,
C.

Excel Macro Number Of Rows And SAVE As Questions
Hello,

I have two questions about a macro I have been writing in an Excel worksheet.

1. How can I get the number of rows in the current worksheet? I need to select all the rows in an Excel worksheet and select them. This code works with an worksheet containing 202 rows, but I need to be able to select any number of rows. My code now looks like:

LastRow = 202
Range("A1:" & "H" & LastRow).Select

How can I set LastRow to the number of rows in the current worksheet?


2. I am saving the output dataset with a SaveAs command. If the dataset already exists, I want to replace it. How can I tell Excel to do that? Right now, my macro always tells me that the dataset already exist and prompts for the OK to replace. My code now looks like:

ActiveWorkbook.SaveAs Filename:= _
"C:WorkFolderWorkMansfield2.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

I've written most of this macro by recording my keystrokes and then editing the resulting macro to do what I want. I'm stuck on these two commands.

Thank you for your help!

Linda
Edit by italkid: Please create your threads in the proper forum, thanks

Count Number Of Rows(records) In An Excel Sheet
I am trying to loop through the records in an excel sheet. I have attempted it and all I can achieve is count the whole worksheet((65536 rows). Do I have to use an ADODB recordset to achieve this ? Here is what I have so far:

Code:
Public Sub RowCount()
Dim counter As Double
For Each rw In Worksheets("brutto").Rows
    counter = counter + 1
Next rw
MsgBox counter
End Sub




[Edit...] Sorry I should have looked a bit more. I found a faq here.




A VB turned PHP geek


Edited by - paCkeTroUTer on 8/3/2003 8:03:26 PM

Copyright 2005-08 www.BigResource.com, All rights reserved