Active sort / Copy

B

BobS

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.
 
J

JLGWhiz

After the autofilter statement, you could put an If statement that If (same
range to copy). SpecialCells(xlCellTypeVisible).Count < 1 Tthen Exit For.
That should take you to the next sheet.

BobS said:
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.
Click to expand...
 
D

Dave Peterson

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


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.
Click to expand...
 
B

BobS

Thanks to you both. The checking if the visible cell count after copying the
data from the array worked nicely.

Thank you,

Bob S.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with a VBA subroutine 0
Finding and Replacing Data in a Header Row 0
Macro to Combine Data from Multiple Sheets with Cell References 4
Delete duplicate rows without deleting entire row 1
Clean up code 3
Copy Destination 5
Dates In Excel 5
Copy columns and paste with blank column between 5

Top