Deleting a row from a table based on 2 criteria (1 Criteria working)

Question

This code is inside of a module and it is activated when i select a cell then press my button "delete note". I was getting constant errors trying to write this so i turned to google and tried someone else's method which used filtering but it wouldn't work and i didn't like how it had to activate the sheet which contained the table anyway so i gave it another go writing my own code and have it almost working.

If i change this line:

If .DataBodyRange.Cells(Counter, .ListColumns("Name").Index) = EmployeeName And .DataBodyRange.Cells(Counter, .ListColumns("Date").Index) = SelectedDate Then
.ListRows(Counter).Delete

To this, then the macro works but obviously its deleted all names linked to that date instead of the name in cell "B3" on Sheet "Calendar"

If .DataBodyRange.Cells(Counter, .ListColumns("Date").Index) = SelectedDate Then
.ListRows(Counter).Delete

If i change it to this, i get a type mismatch error

If .DataBodyRange.Cells(Counter, .ListColumns("Name").Index) = EmployeeName

Full Code Below

    Dim Counter As Integer, EmployeeName As Integer, LastRow As Integer, SelectedDate As Date


    If Intersect(ActiveCell, range("D12:AS23")) Is Nothing Then
        MsgBox "Please select a date.", , "Error"
        Exit Sub
    Else
        If Sheets("Settings").range("Protected") = 2 Then
            With Sheets("Calendar")
                SelectedDate = Cells(ActiveCell.Row, 2) - 1 + ActiveCell.Value
                EmployeeName = range("B3")
            End With
            With Sheets("Notes").ListObjects("TblNotes")
                LastRow = .range.Rows.Count
                For Counter = LastRow To 1 Step -1
                If .DataBodyRange.Cells(Counter, .ListColumns("Name").Index) = EmployeeName And .DataBodyRange.Cells(Counter, .ListColumns("Date").Index) = SelectedDate Then
                    .ListRows(Counter).Delete
                End If
                Next Counter
            End With
        Else
            'do nothing
        End If
    End If

if you put `MsgBox .ListColumns("Name").Index` on the line before that, what pops up? or do you get the error then as well? — braX, Sep 17 '19 at 07:10
hey thanks for replying, i've just fixed it i had to change dim EmployeeName As Integer to dim EmployeeName as String. could you possibly explain why this fixed the issue? i am really good at working stuff out through trial and error without really knowing anything lol i'm less than a week old at coding and i wrote that whole code myself i'm at the stage now where i need to start understanding why things have to be a certain way — Chris R, Sep 17 '19 at 07:21
Note that if your `Else` is `'do nothing` then you can just remove the `Else` part completely. — Pᴇʜ, Sep 17 '19 at 07:26
thanks peh i assumedif i don't state that, it still runs pointlessly in the background — Chris R, Sep 17 '19 at 07:51

score 1 · Accepted Answer · answered Sep 17 '19 at 07:19

1

ok so all i had to do was change dim EmployeeName As Integer to dim EmployeeName as String, everything else worked perfectly :). Can someone explain to me why this fixed it? i am quite good at working things out using trial and error but i dont actually know why this fixed the issue.

answered Sep 17 '19 at 07:19

Chris R

65
7

1

I guess that the value in `EmployeeName` is a text and not a number. So `String` declares the variable to be text and `Integer` declares a variable to be a integer number. Since you cannot push a text into a number variable you need to use `String`. • Anyway your row counting variables must be `Long` not `Integer` because Excel has more rows than fit into `Integer`. – Pᴇʜ Sep 17 '19 at 07:22
thanks bud i will change that. Ok so integer can only ever be a number? string does the same as long but with limitations, why use string at all ever then? – Chris R Sep 17 '19 at 07:53
2

No, you should really read some tutorials/handbook whatever about data types. You are lacking essential basic understanding of variables and types. Here is a short [data type summary](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary) which shows which data type can contain which data. While trial and error might lead you to some result (that may work for you) it does not mean that it is correct. Some things may work by accident but still can be wrong. You must also read the documentations to get things done right. – Pᴇʜ Sep 17 '19 at 08:08
`String` = text • `Integer` = number (without decimals) • `Long` = bigger number (without decimals). Read the documentations! – Pᴇʜ Sep 17 '19 at 08:10
This simple table can also help you to understand [Table here](http://cis.poly.edu/~mleung/CS394/f06/VBA/essentialElements/dataTypes.html) – TourEiffel Sep 17 '19 at 08:12
thanks lads i did try to google this basic information but give up after 30 seconds my mind was on some code i was trying to puzzle out lol. i have tried looking for basic teaching resources and find it strangely hard i really thought there would be lots of learning resources for something like this. there is a lot for python – Chris R Sep 17 '19 at 08:24
so my question from earlier but now actually making sense why use an integer when you can use a long instead? sometimes an integer might not work because it isn't big enough where if you use long as standard you should be fine? – Chris R Sep 17 '19 at 08:26
@ChrisR If you have a look into the link I gave you, you will see that different data types use a different amount of storage (in memory). So using the biggest possible data type is not smart either. • Now `Long` is a bit special (in VBA) because in a 32 bit system `Integer` takes also 4 bytes as `Long` does (not only 2 as stated in the link), so there is actually no benefit in using `Integer` in VBA. See [Why use integer instead of long](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) for further information about this special case. – Pᴇʜ Sep 17 '19 at 09:14
an integer gets converted to a long in the background any way lol i didnt know that. yeh ive been diving into the link you sent me, is this the best place for learning all things vba? i remember when i first got interested in coding years ago there was a cool python app that led you step by step making basic things and explaining everything being used and why. i wish vba had something like that im learning by doing and i know i need to use something in a certain place i just dont know why, i keep trying stuff i think might work until it does work lol its amazing how much you can do like that – Chris R Sep 17 '19 at 09:21
@ChrisR Actually it is not really correct that the `Integer` is *converted* to a `Long` it is just converted in that way that it needs the same amount of memory as the `Long` (4 bytes). But it is still an `Integer` with all its down sides and limitations. So that just means if you have to use 4 bytes anyway, you can also use `Long` which is not as limited as `Integer` and it still needs the same amount of memory. So the benefit here is less limitations while using the same memory. – Pᴇʜ Sep 17 '19 at 09:35
yeh that was an interesting link thanks for digging that up. – Chris R Sep 17 '19 at 09:35

Deleting a row from a table based on 2 criteria (1 Criteria working)

1 Answers1