Use VBA to extract Row number from active cell


Hi all, 
I've been struggling with the code for the last 24ish hours.. 
Background: I'm trying to have a table of data (grocery items) that with a click on 'Add Item Button' a form will be opened - you feed the fields with info and submit to a new line <-- this works fine.
Problem: I've been trying to add a new ERASE line button: 
Clicking it shall promp a UserForm that has a worning label and a safety chekbox 
(while checkbox isn't checked the delete button is disabled) <-so far working great. 
Issue: I fail to extract the ActiveCell ROW number - 
While I can use ActiveCell.Row.DeleteRow - it will harm severals columns that mustn't be deleted. Hence I need to find a way to collect that Row number (ONLY) and then I will be able to select specific columns combination to erase the content of only specific cells. 

Thanks in advance! 
Best Regards,
Eyal <3 :) 



Selected Answer

When you "delete" a row that row gets deleted, meaning removed from the sheet, with everything in it. For the purpose of "erasing" the contents of cells, but not the cells themselves, VBA has the ClearContents method. It works on a Range object, meaning you can apply it to individual cells or larger areas.

You can use ActiveCell.Row to determine the row number of the ActiveCell. You can use it to define a range, perhaps as shown below.

Dim R As Long
Dim Rng As Range

R = ActiveCell.Row
Set Rng = Range(Cells(R, "A"), Cells(R, "C"))
Set Rng = AppliCation.Union(Rng, Range(Cells(R, "F"), Cells(R, "G"))

The above code would erase the contents of ranges A:C and F:G in the row of the ActiveCell. Please be very careful when using ActiveCell. That's because that cell is usually determined by the user, not the code. If you let the user interfere with what your code does there is a high risk. And if you let your code determine the ActiveCell in the above context your code needs re-working. More usually, the row to erase in would be one that your program determined, such as the last row in the worksheet.

Edit Jul 22, 2020   ======================

Referring to your request for clarification in the Discussion below, please try this code.

Private Sub Try()
    Dim Rng As Range
    Dim R As Long

    R = ActiveCell.Row
    Set Rng = Range(Cells(R, "A"), Cells(R, "E"))
    Set Rng = Application.Union(Rng, Cells(R, "G"))
    Set Rng = Application.Union(Rng, Range(Cells(R, "I"), Cells(R, "J")))

    Debug.Print Rng.Address
'    Rng.ClearContents
End Sub

A cell is the smallest possible range. You would normally address it as a member of the Cells collection. The syntax is Cells([Row number], [Column number/name]). When you specify a cell like that you get a Range object. because Excel doesn't have a "Cell" object.

The VBA syntax to define a Range requires specification of the first and last cells. It looks a little laborious but it's simple enough, Range(Cells([Row number], [Column number/name]), Cells([Row number], [Column number/name])).

The problem you came up against is how to define a range of a single cell. That is straight-forward, too. If you want to define it as a Range you must use the syntax for defining a range, like Range(Cells(R, "G"), Cells(R, "G")). But you can also use the syntax for defining a single cell as I have done in my code above.



First, Thank you SO very much for the amazing swift response and accurate code. Thank you for your time and patiance for explaining! <3  Your code worked amazing, however I need to fix something please with my understanding of the code:   
 R = ActiveCell.Row
    Set Rng = Range(Cells(R, "a"), Cells(R, "E"))
    Set Rng = Application.Union(Rng, Range(Cells(R, "G")))
    Set Rng = Application.Union(Rng, Range(Cells(R, "I"), Cells(R, "J")))
  I need to clear content only from: A:E, G, I:J

How do I do it, please? :) 
Eyal David (rep: 2) Jul 21, '20 at 9:07 am
Dear Variatus, How can I ever thank you enough?  Is there any way I may contact you, please?  Thanks in advance!  Best Regards,  Eyal 
Eyal David (rep: 2) Jul 23, '20 at 8:16 am
May I ask another thing please:    I need to add product data to colums(rows_) next empty column/  Problem is that the code that worked last night is not working now:

 Range("A1").End(xlDown).Offset(1).Value = Me.txtbProductName.Value
     Range("B1").End(xlDown).Offset(1).Value = Me.cmbChooseDepartment.Value
     Range("C1").End(xlDown).Offset(1).Value = Me.txtbPrice.Value
     Range("D1").End(xlDown).Offset(1).Value = Me.txtbUnitsNumber.Value
     Range("E1").End(xlDown).Offset(1).Value = Me.cmbChooseUnitType.Value
     Range("G1").End(xlDown).Offset(1).Value = Me.txtbNumOfUnits.Value
     Range("I1").End(xlDown).Offset(1).Value = Me.txtbPurchaseDate.Value
     Range("J1").End(xlDown).Offset(1).Value = Me.cmbChooseSapak.Value

Run-Time error '1004':  Application-defined or object-defined error
Eyal David (rep: 2) Jul 24, '20 at 8:17 am
This isn't the place to ask another question. Just ask another question and one of the experts and fans here will offer their advice.
Variatus (rep: 4544) Jul 24, '20 at 9:40 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login