Would someone PLEASE be able to help me with this!!!!

I am trying to go through a spreadsheet and delete some unwanted data. I have created some if statements in order to determine which data should be deleted etc, however i want to dynamically change the rows that i test.

I have created a variable called starta and use this to store the row that i want to start my loop on. the problem i have is using this variable as part of a select statement.

starta = InputBox("please enter the starting row")

the select statement i have is:

atest = Range("A"&starta&":A"&starta).Value

if, for arguments sake, starta = 23, then i want atest to be like this:

atest = Range("A23:A23").Value

which would mean that atest holds the value of the cell A23

but when I try this line (the one in red) I keep getting an error.

Could someone please offer some assistance.

hey pal,

i think this code might be able to help you.


Public Sub deletingRow()
    Dim rowID As Long
    rowID = Application.InputBox(prompt:="Please enter a row number", Type:=1)
    Range("A1").Cells(rowID, "A").Value = ""
End Sub

ix

This doesn't really help. All this does is clear the contents of cell a<rownumber> however I have already worked out the code for deleting (removing) the rows that i don't want, I just want to be able to enter a number as the macro runs and that number will become part of a string. the string will be the argument for the function "Range" so that atest is now referring to a different row/cell.

any more suggestions?

I have almost sorted it out. It is a bit clumsy, but here is my solution, however there still remains a problem.

The solution i have is:

rowID = Application.InputBox(prompt:="Please enter a row number", Type:=1)

    arow = "A" & rowID
    brow = "A" & rowID + 1
    crow = "B" & rowID
    drow = "B" & rowID + 1

    atest = Range(arow).Value
    btest = Range(brow).Value
    ctest = Range(crow).Value
    dtest = Range(drow).Value

The problem remains when I try to delete the unwanted rows. I have used the following code:

delrows:

    rowIE = rowID + 1
    MsgBox ("Deletion of rows " & rowID & " & " & rowIE & " about to occur")
    Rows("rowID:rowIE").Select
    Selection.Delete Shift:=xlUp

however Excel throws up an error with the "rowID:rowIE" statement. this statement works if i put in actual numbers instead of variables, however I need to be able to put in variables as the argument to the Rows function.

in other words: Rows(2:3).Select works fine but Rows(rowID:rowIE).Select where rowID=2 and rowIE=3 doesn't work.

How to do this?

I've managed to sort it out. I was having trouble with the white spaces. I'm used to programming in C where whitespace isn't an issue, so i tried a couple of combos and found that the following worked fine:

Rows("" & rowID & ":" & rowIE).Select

I had no trouble with just a few spaces in your original code, ie atest = Range("A" & starta & ":A" & starta).Value