Macro to delete rows from "Notes' in Col A to last row containing data in Col B

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I have the following code to delete all rows from where "Notes" appears in Col A on Sheet Data Import up to last row containing data in Col B


It would be appreciated if someone could kindly amend my code

I am unsure how to locate last row in Col b containing Data

Code:
 Sub Delete_Rows_fromNotes()
     Set rStartCell = .Cells.Find(What:="NOTES", LookAt:=xlWhole)

        If Not rStartCell Is Nothing Then

              Set rEndCell = .Cells.Find("???", LookAt:=xlWhole)

              If Not rEndCell Is Nothing Then

                    Set rEndCell = rEndCell.Offset(iEXTRA_ROWS, 0)

                    Range(rStartCell, rEndCell).EntireRow.Delete

            

              End If

        

        End If

    End With

End Sub
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
How about
VBA Code:
Sub howard()
   With Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row)
      .Replace "Notes", True, xlWhole, , False, , False, False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
      On Error GoTo 0
   End With
End Sub
 
Upvote 0
Solution
thanks for he help Fluff

The code is only deleting the row containing "Notes"

I want all the rows from Notes to be deleted up to the last row containing any data/text

you will see a few strange characters, which is the result of a PDF file being converted into Excel and imported



I have highlighted to data to be deleted in yellow see sample data below

Macro to detete Rows from Notes to last row containing Data.xlsm
ABCDEFGH
12931239YEJNominal Ledger07/05/202101291.03-1291.03
229312529YEJNominal Ledger07/05/20212143.98802143.988
3299507YEJNominal Ledger07/05/2021659.250659.25
42931228YEJNominal Ledger07/05/202101409.024-1409.02
5312828312YEJNominal Ledger10/05/202108152.8-8152.8
629312394YEJNominal Ledger11/05/202104365.646-4365.65
7293128YEJNominal Ledger13/05/2021029.8-29.8
831285312YEJNominal Ledger13/05/20210150-150
931285312YEJNominal Ledger13/05/20210158.594-158.594
10293123124YEJNominal Ledger13/05/202102004.912-2004.91
11292329YEJNominal Ledger14/05/20210214.66-214.66
12292312299YEJNominal Ledger17/05/2021429.320429.32
13LPT76431229312YEJNominal Ledger17/05/20210214.66-214.66
14
15
16
17
18 BR1 LIMITED NOTES
19for the year ended 30 June 2020
201.1 GENERAL INFORMATION
21
22Q LU
23
24
25
26
27§
28
29
30
31
32
33
34
35
36LU cc:
Sheet1
Cell Formulas
RangeFormula
H1:H13H1=+F1-G1
 
Upvote 0
How about
VBA Code:
Sub howard()
   Dim Fnd As Range
   
   Set Fnd = Range("A:A").Find("notes", , , xlPart, , , False, , False)
   If Not Fnd Is Nothing Then Range(Fnd, Range("A" & Rows.Count)).EntireRow.Delete
End Sub
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top