Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Data
I have one column with Dates and several columns with Instrument
readings, eg. Inst1, Inst2, Inst3, etc.The instruments are not read everyday so some of the dates have blank data or zero value in the Instruments columns. How can I write a macro to filter out the dates with a non-zero or non-blank Instrument reading and populate in a different sheet for each Instrument? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Data
The code below is close to being right. I don't know where the instrument
names are located from your posting. I think this line in the code will need to be changed SHName = Cells(RowCount, _ ColumnCount).Value right now the code is using the reading value as the sheet names. The results will look strange. Sub copyinstruments() Const DateColumn = "A" Const InstrumentStartCol = "B" Const InstrumentEndCol = "F" StartCol = Cells(1, InstrumentStartCol).Column EndCol = Cells(1, InstrumentEndCol).Column LastRow = Cells(Rows.Count, DateColumn). _ End(xlUp).Row Set FirstSheet = ActiveSheet For RowCount = 1 To LastRow For ColumnCount = StartCol To EndCol If Not IsEmpty(Cells(RowCount, _ ColumnCount)) Then SHName = Cells(RowCount, _ ColumnCount).Value 'check if worksheet exists Found = False For Each ws In Worksheets If ws.Name = SHName Then Found = True Exit For End If Next ws If Found = False Then Worksheets.Add after:= _ Worksheets(Worksheets.Count) ActiveSheet.Name = SHName FirstSheet.Activate End If With Worksheets(SHName) If Found = True Then LastRow = .Cells(Rows.Count, _ "A").End(xlUp).Row NewRow = LastRow + 1 Else NewRow = 1 End If .Cells(NewRow, "A") = _ Cells(RowCount, DateColumn).Value .Cells(NewRow, "B") = _ Cells(RowCount, ColumnCount).Value End With End If Next ColumnCount Next RowCount End Sub " wrote: I have one column with Dates and several columns with Instrument readings, eg. Inst1, Inst2, Inst3, etc.The instruments are not read everyday so some of the dates have blank data or zero value in the Instruments columns. How can I write a macro to filter out the dates with a non-zero or non-blank Instrument reading and populate in a different sheet for each Instrument? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data sorting | Excel Worksheet Functions | |||
Sorting of data | Excel Worksheet Functions | |||
sorting data | Excel Programming | |||
Sorting data to match existing data | Excel Discussion (Misc queries) | |||
colors of bar charted data don't follow data after sorting | Charts and Charting in Excel |