0

I'm trying to create a macro that will automate a report. Issue is, the source data brings through data we don't need, and the report itself does lots of calculations based on the table as a whole. There's hundreds of calculations being done based on the table so adding in an inclusion parameter to each formula in the report is going to take a prohibitive amount of time.

This is easily worked around when updating the report manually, by using the filter, filtering data out, and deleting the relevant data. I'm trying to automate the file however, so we can just click a button and refresh it without having to spend 20-30 minutes doing so.

What I therefore want to do, but have failed to work out so far, is have VBA delete every row where a column contains a certain string.

This is as part of a larger macro, which updates data, corrects a dates column using Text to Columns, deletes a section of data, calculates the formulas in the KPI's sheet, and then saves it as this weeks date.

I can automate all of the other steps ok as I've already created several macros to update other reports, but this has stumped me! My Google-Fu has failed me too.

2
  • What have you tried? Where did it go wrong? Were you able to delete a row at all? Share your code so we can see what you've done.
    – Dave
    Sep 29, 2015 at 8:54
  • To be honest Dave, its more that I have no idea where to start! I understand that I can create an if statement and then delete the rows. but I'm stumped as to the exact how to.
    – Miller86
    Sep 29, 2015 at 9:04

1 Answer 1

1

This sub deletes the entire row(s) of a sheet when a value defined by the variable thestring is in the column defined by the variable thecolumn

Sub deleterows()
    Dim a As Application
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim DataRange As Range
    Set a = Application
    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    wks.Application.ScreenUpdating = False
    thecolumn = 2 'this have to be changed to the desired column
    thestring = "some text" ' this have to be changed too
    reviewing = True
    visitrow = 1
    While reviewing = True
        visitrow = visitrow + 1
        If wks.Cells(visitrow, 1) = "" Then
            reviewing = False
        End If
        celldata = wks.Cells(visitrow, thecolumn)
        If IsNumeric(celldata) = True Then
            celldata = Trim(Str(celldata))
        End If
        If celldata = thestring Then
            wks.Rows(visitrow).Delete
            visitrow = visitrow - 1
        End If
    Wend
   wks.Application.ScreenUpdating = True
   Message = MsgBox("Finished!", vbOKOnly)
End Sub
1
  • Excellent, Thanks @jcbermu - Much appreciated :) If I understand the code correctly, this looks like it should work. I'll try it out later when I have time to fully write the macro.
    – Miller86
    Sep 29, 2015 at 10:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .