How to know the total number of records in the excel sheet
 

Hi,
Could any help me out?

I have a VBA application where i need to know the number of records i
the sheet1. Example if
1ts row = A
2nd row = b
3rd row = empty
4 th row =empty
5th row = p

then i should get 5 as the record count. Currently i am checking al
the rows one by one which is taking long time to get the result.

Also i need to know if any of the rows between 1 to 5 is empty then
need a message box to be displayed. Is there any function to get th
no. of rows and number of empty rows

for example

No of rows = 5
and No of empty rows = 2

Thanks in advance

Regards
Venkates

--
Message posted from http://www.ExcelForum.com

How to know the total number of records in the excel sheet
 

Dim rng as Range, rng1 as Range
set rng = Application.UsedRange.SpecialCells(xlConstants).En tireRow
set rng = Intersect(rng,Columns(1)
set rng1 = cells(rows.count,1).End(xlup)
msgbox "rows: " & rng1.row & vnNewline & _
"blanks: " & rng1.row - rng.count & vbNewLine & _
"filled: " & rng.count

You can also use:

Dim rng as Range
set rng = Range(cells(1,1),cells(rows.count,1).End(xlup))
rws = Application.CountA(rng)
blnks = Application.countblank(rng)

msgbox "rows: " & rws & vbNewline & _
"blnks: " & blnks

--
Regards,
Tom Ogilvy

"kvenku " wrote in message
...
Hi,
Could any help me out?

I have a VBA application where i need to know the number of records in
the sheet1. Example if
1ts row = A
2nd row = b
3rd row = empty
4 th row =empty
5th row = p

then i should get 5 as the record count. Currently i am checking all
the rows one by one which is taking long time to get the result.

Also i need to know if any of the rows between 1 to 5 is empty then i
need a message box to be displayed. Is there any function to get the
no. of rows and number of empty rows

for example

No of rows = 5
and No of empty rows = 2

Thanks in advance

Regards
Venkatesh


---
Message posted from http://www.ExcelForum.com/

How to know the total number of records in the excel sheet
 

This code works:

Sub CheckColumn()
Dim NumberOfRows As Long, NumberOfEmpty As Long

' Go to the bottom of the column. Change the column
letter if required
Range("A65536").Select
' Go to the last non-empty cell
Selection.End(xlUp).Select
' This cell gives the number of rows
NumberOfRows = ActiveCell.Row
' Select from here to the top and use CountBlank to give
the number of empty columns
Range("A1:A" & NumberOfRows).Select
NumberOfEmpty = Application.CountBlank(Selection)

MsgBox "No of rows: " & NumberOfRows & vbNewLine & "No of
empty rows: " & NumberOfEmpty

End Sub




-----Original Message-----
Hi,
Could any help me out?

I have a VBA application where i need to know the number
of records in
the sheet1. Example if
1ts row = A
2nd row = b
3rd row = empty
4 th row =empty
5th row = p

then i should get 5 as the record count. Currently i am
checking all
the rows one by one which is taking long time to get the
result.

Also i need to know if any of the rows between 1 to 5 is
empty then i
need a message box to be displayed. Is there any function
to get the
no. of rows and number of empty rows

for example

No of rows = 5
and No of empty rows = 2

Thanks in advance

Regards
Venkatesh


---
Message posted from http://www.ExcelForum.com/

.

How to know the total number of records in the excel sheet
 

I have a question again. I changed the given code as the below. The
problem is in your code is it will check for only for one columns where
in my case i need to check all the 255 columns. SO i changed the code as
the following and both the no of rows and the no of blanks are wrong.

Please help me out

Thanks
Venkatesh

Columns("A:IV").Select
' Go to the last non-empty cell
Selection.End(xlUp).Select
' This cell gives the number of rows
NumberOfRows = ActiveCell.Row
' Select from here to the top and use CountBlank to give
'the number of empty columns
Columns("A:IV").Select
'Range("A:IV" & NumberOfRows).Select
NumberOfEmpty = Application.CountBlank(Selection)

MsgBox "No of rows: " & NumberOfRows & vbCrLf & "No of empty rows: " &
NumberOfEmpty


---
Message posted from http://www.ExcelForum.com/