Re: Active sort / Copy

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

After you filter the data, you can look at how many visible rows are in the
autofilter.range. If there's only one (the headers count), then you don't have
any data.

'actually just use the first column and count visible cells
dim VCells as long
vcells = activesheet.autofilter.range.columns(1) _
.cells.specialcells(xlcelltypevisible).cells.count
if vcells = 1 then
'only the headers
else
msgbox vcells -1 & " rows of data found!"
end if



BobS wrote:

Novice
Excel 2003

My additions to a "ImportBigFile" macro by C. Pearson is working nicely
thanks to several here - thanks. Found I have a wee bit of a problem though
if there are no entries for a device.

Here's what I'm doing briefly:

Have a log file that has 200,000 + records

I import it and fill a worksheet to 64K, then start another until the EOF is
reached. This raw data has a header row at the top of each page (row 5) and
I'm only using columns A-F on each page of raw data.

I then add a sheet for each of the 30 devices and then copy the header from
the first raw data sheet over to the 30 device sheets. The raw data is then
sorted using the active filter and all the imported raw data is then copied
over to it's respective device sheet. Works great. All device numbers in
the raw data sheets end up on the correct sheets - no problems.

But.... if there are no entries (log records) for say device 21, for some
reason as each of the raw data sheets are scanned and no data is
found/filtered, an additional header row is placed under the original header
row on the device sheet. This will occur for each raw data sheet that is
filtered.

So if I have 5 sheets of raw data, they are all scanned, no data found for
the device (since there is none) and then 5 additional header rows are
placed below row 5's.

Here's a snippet of the code:

I = 0
For I = 1 To RawDataShts 'Filter the raw imported data on each Data
sheet and sort it, then copy it to it's respective number sheet
Worksheets(I).Activate
UnitNum = 0
For T = 1 To 30

UnitNum = UnitNum + 1 'filter on the Unit Number of the equipment

'routine is sorting on the Unit number.
If T < 10 Then
[B5].CurrentRegion.AutoFilter Field:=2, Criteria1:="=*RTMS
0" & T & "*" 'filter can't deal with 01, 02...09 numbers. It drops leading
zero so use the "0" in front to filter on
Range([A5], Range("F" &
[A5].SpecialCells(xlLastCell).Row)).SpecialCells(xlCellTypeVisible).Copy _
Sheets("RTMS-" & UnitNum).Range("A" & Sheets("RTMS-" &
UnitNum).[A5].SpecialCells(xlLastCell).Row + 1)

Else 'this catches devices number 10 to 30
[B5].CurrentRegion.AutoFilter Field:=2, Criteria1:="=*RTMS "
& T & "*" 'this get everyting else for units 10 to 30
Range([A5], Range("F" &
[A5].SpecialCells(xlLastCell).Row)).SpecialCells(xlCellTypeVisible).Copy _
Sheets("RTMS-" & UnitNum).Range("A" & Sheets("RTMS-" &
UnitNum).[A5].SpecialCells(xlLastCell).Row + 1)
End If

Next T
Selection.AutoFilter 'turn off the autofilter function on the Data
sheets
Next I

How can I test if a device number has no data and simply jump out and on to
the next one, or test for no data and decrement the row count so that row is
overwritten?

Thanks,

Bob S.

--

Dave Peterson
.

Relevant Pages

  • Re: Help from Dave Peterson - no dis-respect to all other experts
    ... The original code just copied rowsas the header. ... Dim DestCell As Range ... Create New Sheets from Filtered List -- uses an Advanced Filter to create ... Master sheet to individual worksheets -- replaces old data with current. ...
    (microsoft.public.excel.misc)
  • Re: Filtering info to corresponding sheet
    ... First cell header is A1 ... I need to filter by column C ... not use xlFilterCopy to filter and copy the data to a new sheet or workbook? ... Dim wsNew As Worksheet ...
    (microsoft.public.excel.programming)
  • Re: Creating New Worksheets for identical cells
    ... You could apply a filter to the Dist column, ... Highlight the visible rows (including header), ... employees matching 007 will be included on that sheet. ...
    (microsoft.public.excel.programming)
  • Re: help in using count if,,
    ... Put Email in A1 (just as a header) ... Copy the 2 lists into column A of this new sheet ... You can filter to show the Falses in any column to see where its missing. ...
    (microsoft.public.excel.misc)
  • Re: Pivot Chart question
    ... Select the raw data and press Alt+D, ... At the bottom right corner of the pivot table double-click the Grand Total ... series filter to choose which companies you want to look at. ... So i have a header for Symbol, But my date is technically my header ...
    (microsoft.public.excel.misc)