CODEWITHSUNDEEP
vbaexcel
user1681664
user1681664

Reputation: 1811

cleaning excel sheet with vba

I have an excel sheet with lots of data. As you may know, this comes with lots of problems. One major one is having too much data. I am not familiar with vba, but I wanted to know how to clean data.

I have a sheet with 3 fields: date, time, and temp. The temperature is recorded on a minute by minute basis. The temperature is only recorded from 7 am to 10 pm, but the sheet is on a 24 hour basis. So my sheet has a lot of blank cells. So, I want to write a code that states:

if ((time < 7am) or (time > 10pm)):
         delete row

Can I do this?

Also, another problem is that the data is not collected on weekends. I am not given a day field, only a date field in this format: 20130102 which is January 02 2013. I want to:

if ((date = saturday) or (date = sunday)):
          delete row

Are either of these doable?

My sheets looks like the following:

A .............. B ......... .... C

date........ time ......... temp

enter image description here

Upvotes: 1

Views: 704

Answers (2)

igelineau
igelineau

Reputation: 763

You can do it this way, assuming the column that contains your date is the 2nd (B) :

Dim i As Integer
for i = 1 to cellsCount
    If Hour(Cells(i, 2)) < 7 Or Hour(Cells(i, 2) > 22 Then
        Rows(i).Delete
    Else If WeekDay(Cells(i, 2)) = 7 Or WeekDay(Cells(i, 2)) = 1 Then
        Rows(i).Delete
    End If
next

You can have more information about the WeekDay function here : http://msdn.microsoft.com/en-us/library/82yfs2zh%28v=vs.90%29.aspx

Upvotes: 2

ARich
ARich

Reputation: 3279

Since both your dates and times are formatted differently than normal, we need to manipulate the values to get something to test against. Consider the following example (I've commented each line to help you follow along):

Sub DeleteRows()

Dim lastRow As Long
Dim Cell As Long
Dim dt As Date

'Work with the active sheet.
With ActiveSheet

    'Find the last row of your dataset.
    lastRow = .Range("A:A").Find("*", searchdirection:=xlPrevious).Row

    'Format your time column to a readable time.
    .Columns("B").NumberFormat = "[$-F400]h:mm:ss AM/PM"

    'Loop through the rows, beginning at the bottom.
    For Cell = lastRow To 2 Step -1

        'Piece together the date.
        dt = Mid(.Cells(Cell, 1), 7, 2) & "/" & _ 
             Mid(.Cells(Cell, 1), 5, 2) & "/" & Left(.Cells(Cell, 1), 4)

        'If the date is a Sat or Sun, delete the row.
        If Weekday(dt) = 1 Or Weekday(dt) = 7 Then
            .Rows(Cell).EntireRow.Delete

        'If the time is before 7am or after 10pm, delete the row.
        ElseIf Hour(.Cells(Cell, 1)) < 7 Or Hour(.Cells(Cell, 1)) > 22 Then
            .Rows(Cell).EntireRow.Delete
        End If
    Next Cell
End With

MsgBox "Done!"

End Sub

A few things to note about the code. First, we must start at the bottom of the list because as we delete rows, the remaining rows shift upwards. If we were to go from top to bottom (e.g. A1 to A10), if we deleted row 5, row 6 would slide into its place, and the loop would skip row 5 (previously row 6) and go on to row 6. In other words, looping from top to bottom when deleting rows will ultimately skip rows unintentionally.

Second, I had to guess on your time format. While I believe I guessed correctly, I may not have. If I was wrong and my code doesn't change the time column into a readable time, record a macro while changing the format of that column and substitute the new format with mine ("[$-F400]h:mm:ss AM/PM" ).

And lastly, since your date column is an abnormal format (for Excel), we need to reorder the date so that Excel can read it. Once we've done that, we can use the resulting date to see if the date was a Sat. or Sun.

Upvotes: 3

Related Questions