Spaces in data source

Resolved · Low Priority · Version 2007

Mon 7 Nov 2011, 12:35 Delegate Barry said...

Barry has attended:
Excel VBA Intro Intermediate course

Spaces in data source

Hi Guys

Wonder if you can help me.

I have a range of data with phone records which need to be counted by service number then data filled into another sheet.

This sheet has a list of numbers down the left hand side which some are broken up by blank rows.

I need the macro to jump over these blank rows and continue to search through the service numbers until there are no more.

The problem i have is i cant use range due to these spaces and can't remove the spaces.

Any help would be greatful.

Cheers

Thu 10 Nov 2011, 10:52 Trainer Anthony said...

RE: Spaces in data source

Hi Barry, thanks for your query. Here's some rather rough code to count items in a range, skipping blank rows:

-----------------------------
Sub test()

Dim introwcount As Long
Dim mycounter As Integer

mycounter = 0

For introwcount = 1 To 65000

If Cells(introwcount, 1).Value <> "" Then

mycounter = mycounter + 1

End If

Next introwcount

MsgBox mycounter

End Sub

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

Hope this helps,

Anthony

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Repeating headings on spreadsheets that print on more than one page

By default when you print a spreadsheet out and it prints on more than one page, the headings at the top and the side of the spreadsheet don't appear on all the pages following page 1.

To get Excel to repeat headings on all pages when printing, go to File - Page Setup - Sheet, then select the rows to repeat at the top of pages, and the columns to repeat at the side of pages by clicking on the red arrows at the right side of the two boxes under the 'Print titles' area. Then click OK.

If you view your spreadsheet in Print Preview, you should see the headings being repeated on each page.

View all Excel hints and tips