0

I'm really new to programming and I have a spreadsheet I'm working on that I could use some help with. My Columns A and B are two dropdown menus, where B is dependent on A. There are some other columns that use VLookUp to bring up information based on the dropdown, and one other column where I have a simple formula. If I clear out a value from Column "A", I would like the entire row to clear (without losing my formulas), and i would all the cells with values under it to shift up. I've looked up a lot of ways to do this with deleting cells, but I haven't been able to find much with clearing them. My code almost works, but seems pretty inelegant since I'm using the clipboard. I also need to clear out the last row at the end using this code, but when I try it, Excel crashes. Any advice would be most appreciated. Here is my code so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
Dim FirstRow As Long
Dim FirstColumn As Long
Dim LastRow As Long
Dim QtyColumn As Long
Dim FormulaRange1 As Range
Dim FormulaRange2 As Range

FirstColumn = 1
QtyColumn = 4
Set wks = ActiveSheet

If Not Application.Intersect(Target, Range("A11:A26")) Is Nothing Then
   If IsEmpty(Target.Value) Then
   wks.Range("B" & Target.Row).ClearContents
   wks.Range("D" & Target.Row).ClearContents
   FirstRow = Cells(Target.Row, Target.Column).Row
   LastRow = Cells(Rows.count, 1).End(xlUp).Row
   Set FormulaRange1 = wks.Range(Cells(Target.Row, Target.Column).Offset(1, 0), wks.Cells(LastRow, "B"))
   Set FormulaRange2 = wks.Range(Cells(Target.Row, "D").Offset(1, 0), wks.Cells(LastRow, "D"))
   FormulaRange1.Copy
   wks.Range(Cells(FirstRow, FirstColumn).Address).PasteSpecial xlPasteValues
   FormulaRange2.Copy
   wks.Range(Cells(FirstRow, QtyColumn).Address).PasteSpecial xlPasteValues
   Application.CutCopyMode = False
  End If
End If
End Sub
MelissaR
  • 11
  • 2
  • 1
    You are not disabling events so your macro is calling itself over and over until your system crashes. This is a duplicate - please see link below – urdearboy Nov 13 '18 at 16:29
  • Possible duplicate of [Bug on Worksheet Change event in Excel?](https://stackoverflow.com/questions/50868939/bug-on-worksheet-change-event-in-excel) – urdearboy Nov 13 '18 at 16:30
  • Seems like you are over complicating the task. Why not just delete the corresponding row when A is switched to blank? – urdearboy Nov 13 '18 at 16:35
  • Hi urdearboy, Good to know, thanks. That part of the post may be a duplicate, but i hadn't really looked into that part yet. I'm really trying to see if I can get the clear and shift part to work, and maybe be a little less complicated. – MelissaR Nov 13 '18 at 17:13
  • The spreadsheet has a lot of formatting, and if I delete the rows, I lose all of the formatting too. – MelissaR Nov 13 '18 at 17:14
  • Your shift is crashing the macro - even if it’s working perfect, your macro will crash. Follow the instructions on that post, then you will actually get to see what the final result looks like – urdearboy Nov 13 '18 at 17:52

0 Answers0