Hi everyone.
I recently got help here on the forum to get a VBA code that adds products with autofilter through a form with LisBox.
However, in the original code, items start to be added from cell A2.
I would like to start getting records from cell A14.
This seems a little simple, but as the code contains filters, I always end up misconfiguring the script that registers the cells and columns correctly.
Below is my code and attached is my spreadsheet.
Where should I change so that the records are inserted from cell A14?
I recently got help here on the forum to get a VBA code that adds products with autofilter through a form with LisBox.
Identify records and add duplicates. (Consolidate)
Hi everyone! I have a spreadsheet with product registration with the columns: ID, Product, Amount, Price, Total and Product type These products are loaded into a sales registration form. In this form I select the category: "Product type" and in the listbox the respective products of that...
www.mrexcel.com
However, in the original code, items start to be added from cell A2.
I would like to start getting records from cell A14.
This seems a little simple, but as the code contains filters, I always end up misconfiguring the script that registers the cells and columns correctly.
Below is my code and attached is my spreadsheet.
Where should I change so that the records are inserted from cell A14?
VBA Code:
Private Sub btn_inserirpartida_Click()
Application.ScreenUpdating = False
Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet, desWS As Worksheet, key As Variant, n, i As Integer
Dim ultimalinha As Long, fVisRow As Long, lVisRow As Long, ID As Range, totE As Double, totH As Double, rowCount As Long, x As Long
Set ws = ThisWorkbook.Sheets("Pedido")
ws.Activate
n = ws.Range("A1").CurrentRegion.Rows.Count + 1
i = ListBoxProdutos3.ListCount - 1
Range(ws.Cells(n, 1), Cells(n + i, 8)).Value = ListBoxProdutos3.List
ultimalinha = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set RngList = CreateObject("Scripting.Dictionary")
For Each Rng In Range("B2", Range("B" & Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
RngList.Add Rng.Value, Nothing
End If
Next
For Each key In RngList
With ActiveSheet
.Cells(1, 2).CurrentRegion.AutoFilter 2, key
rowCount = .[subtotal(103,B:B)] - 1
If rowCount > 1 Then
fVisRow = .Range("A2:A" & ultimalinha).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
lVisRow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each Rng In .Range("D" & fVisRow & ":D" & lVisRow).SpecialCells(xlCellTypeVisible)
totE = totE + Rng
totH = totH + Rng.Offset(, 2)
Next Rng
.Range("D" & fVisRow) = totE
.Range("G" & fVisRow) = totH
For x = ultimalinha To fVisRow + 1 Step -1
If .Rows(x).Hidden = False Then .Rows(x).Delete
Next x
End If
End With
totE = 0
totH = 0
Next key
Range("B1").AutoFilter
Unload Me
Application.ScreenUpdating = True
MsgBox "Sale successfully registered!"
Sales Filter VBA.xlsm
drive.google.com