PDA

View Full Version : exclude a row in code



sjohnp2112
04-05-2017, 01:58 PM
Hello all,

I have a budget spreadsheet with expense categories in column A. Whenever a transaction value is entered into a cell in a row category the Excel comment box displays automatically where I can add a comment, etc. The last row in the sheet is the "Totals" row that sums daily transactions for the day. One row above this row is the "taxes" row (for sales tax).

What I would like to do is exclude all cells in the sales tax row from the code (no comments), sort of like "if value in column A = "Misc. Sales Tax" and the active cell is in the same row, exit sub or something similar. The problem is the row is not fixed, i.e., the number of categories (in column A) that someone wants to budget for will vary from month to month. One month the Misc.Sales Tax row might be on row 28, the next month it might be on row 33 for example (see attached jpeg). Sounds like I need a For Next loop? I'm still learning vba but not quite there yet.

Could someone help?

thanks, sjohnp2112

jolivanes
04-05-2017, 06:55 PM
from the code
Show us the code please.

Or maybe


Sub Maybe()
Dim lr As Long, lc As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(lr, 2), Cells(lr, lc)).Formula = "=Sum(R1C:R" & lr - 2 & "C)"
End Sub