Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding total # of records in an Excel file without scrolling | Excel Discussion (Misc queries) | |||
show number of selected records in Excel | Excel Discussion (Misc queries) | |||
how to calculate with vba the number of records in an excel- table? | Excel Discussion (Misc queries) | |||
Does Excel 2000/2003 have a max number of records like '98 has? | Excel Discussion (Misc queries) | |||
total number of records | New Users to Excel |