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




Using CountA


G'day guys and gals,

I want to add all non-blank cells within a range. My code thus far filters the raw data for all entries between two dates, copies, then pastes the filtered data into a worksheet.

There are two problems. 1. First of all, the following CountA only returns the value 1 rather than the count of all non-blank cells:


Code:
intLastRow = Sheets("Frtnghtly Rprt").Cells.SpecialCells(xlLastCell).Row
Range("H" & intLastRow).Select
ActCel = ActiveWindow.RangeSelection.Address
Range(ActCel).Offset(1, 0).Select
intCountAH = WorksheetFunction.CountA("H1:" & ActCel)
ActiveCell = intCountAH

Any suggestions on what I am doing wrong here?

2. Next, each time I run the procedure, intLastRow is one number greater than the previous run - even though I delete all cell entries at the start of this procedure (the worksheet this copies filtered data to needs to be completely written-over each time). In other words, if I run the procedure over and over again using the same dates, the ActCel keeps moving down the sheet in increments of one cell each time. Here is the code for deleting all entries, which appears right after the sheet is activated (3rd line of code for this procedure):


Code:
Cells.Select
Selection.ClearContents

Any hints would be appreciated,

Mitchys




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Anybody Know The Code For VBA Excel COUNTA?
I need to know what the code would be for COUNTA(), doesn't have to be exact. This function is used in VBA for Excel office XP

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