Code: Select all
' [a] Clear all contents
Sheets(outSht).Select
Sheets(outSht).Activate
totalRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range(Cells(1, 1), Cells(totalRow, 11)).ClearContents ' Keep its format.
ActiveSheet.Range(Cells(2, 1), Cells(totalRow, 8)).NumberFormat = "0"
ActiveSheet.Range(Cells(2, 9), Cells(totalRow, 11)).NumberFormat = "0.00"
' Get the drain data!
maxRowIn = Sheets(inSht).Cells(Rows.Count, "A").End(xlUp).Row
' Header
ActiveSheet.Range(Cells(1, 1), Cells(1, 11)).Value = _
Worksheets(inSht).Range(Worksheets(inSht).Cells(1, 1), Worksheets(inSht).Cells(1, 11)).Value
Worksheets(outSht).Cells(1, 11) = "Q(gpm)"
outRow = 2
For i = 2 To maxRowIn
keyWd = Sheets(inSht).Cells(i, "A")
rowNo = Sheets(inSht).Cells(i, "B")
If keyWd = "Drain" And minRow <= rowNo And rowNo <= maxRow Then
ActiveSheet.Range(Cells(outRow, 1), Cells(outRow, 10)).Value = _
Worksheets(inSht).Range(Worksheets(inSht).Cells(i, 1), _
Worksheets(inSht).Cells(i, 10)).Value
ActiveSheet.Cells(outRow, 11) = ActiveSheet.Cells(outRow, 9) / (-192.5)
outRow = outRow + 1
End If
Next i