Sub doTheWork()
Dim wks As Worksheet
Dim rng As Range
Dim lastCol As Long
Dim xInput As Long
Set wks = ActiveSheet
xInput = InputBox("Enter Row from which to start deleting", 1)
lastCol = wks.Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
if lastCol > 1 Then wks.Range("B" & xInput, wks.Cells(wks.Rows.Count, lastCol)).Clear
End Sub
Sub doTheWork()
Dim wks As Worksheet
Dim rng As Range
Dim lastCol As Long
Dim xInput As Long
Set wks = ActiveSheet
xInput = InputBox("Enter Row from which to start deleting", 1)
lastCol = wks.Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
if lastCol > 1 Then wks.Range("B" & xInput, wks.Cells(wks.Rows.Count, lastCol)).Delete shift:=xlToLeft
End Sub
Option Explicit
Sub doTheWork()
Dim wks As Worksheet
Dim rng As Range
Dim rDelete As Range
Dim lastCol As Long
Dim xInput As Long
Dim r As Range
Set wks = ActiveSheet
xInput = InputBox("Enter Row from which to start deleting", 1)
lastCol = wks.Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
Set rng = wks.Range("A" & xInput, wks.Range("A" & wks.Rows.Count).End(xlUp))
For Each r In rng
'check last column in this row, if > 1 then delete entire row
lastCol = wks.Rows(r.Row).Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
If lastCol = 1 Then
If rDelete Is Nothing Then
Set rDelete = r
Else
Set rDelete = Union(rDelete, r)
End If
End If
Next r
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub
Option Explicit
Sub doTheWork()
Dim wks As Worksheet
Dim rng As Range
Dim rDelete As Range
Dim lastCol As Long
Dim lastRow As Long
Dim xInput As Long
Dim r As Range
Set wks = ActiveSheet
xInput = InputBox("Enter Row from which to start deleting", Default:=1)
lastRow = wks.Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set rng = wks.Range("A" & xInput, wks.Range("A" & lastRow))
For Each r In rng
'check last column in this row, if <=2 then delete entire row
On Error Resume Next 'in the case of blank rows, then ignore error
lastCol = wks.Rows(r.Row).Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
On Error GoTo 0
If lastCol <= 2 Then
If rDelete Is Nothing Then
Set rDelete = r
Else
Set rDelete = Union(rDelete, r)
End If
End If
Next r
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub
DaveTitle | # Comments | Views | Activity |
---|---|---|---|
Excel 2013 Problem | 12 | 44 | |
Multi vLookup (Excel vba) | 12 | 26 | |
Splitting out Data | 14 | 27 | |
Excel Autofill Dropdown List with Combobox : How to make use of Tab and Enter key to input a value. | 5 | 18 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
12 Experts available now in Live!