• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete rows but not specific column

K

kds14589

New Member
Please remember to include links to any cross-posts
I wish to clear or delete rows in every column EXCEPT column 'A' based on the 'last row' in column 'B' starting in row '4'. For example, if I have a value in B15, then all rows below and to the right would be cleared or deleted but in values in column 'A' would not be harmed.
 
herofox said:
Upload excel filr please as Example ,to not make which you want more than one time ?!!!
Click to expand...
I've attached a filebook i usually send, that way no sensitive date is released. ITS on page named 'delete'
 

Attachments

  • Book1 10.21.21.xlsm
    322.1 KB · Views: 7
Try this.
This will delete all rows below the last value in column B
Code: 
Sub Belle()
Dim lastRow As Long
Dim row As Long
Dim temp As String
' insert your sheet name here
With ThisWorkbook.Worksheets("Delete.Rows")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
' you can change the starting row, right now its 4
    For row = 4 To lastRow
' store whats in col A in a temporary variable
        temp = Trim(CStr(.Range("A" & row).Value))
            If .Range("B" & row).Value = "" Then
                .Rows(row).ClearContents
' place temp variable value back in col A
                .Range("A" & row).Value = temp
            End If
    Next
End With
End Sub
 
Code: 
Sub blah()
With ThisWorkbook.Sheets("Delete.Rows")
  lr = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
  Set rngToDelete = Intersect(.UsedRange, .Range(lr & ":" & .Rows.Count).Resize(, .Columns.Count - 1).Offset(, 1))
  If Not rngToDelete Is Nothing Then rngToDelete.Delete
End With
End Sub
 
Belleke said:
Try this.
This will delete all rows below the last value in column B
Code: 
Sub Belle()
Dim lastRow As Long
Dim row As Long
Dim temp As String
' insert your sheet name here
With ThisWorkbook.Worksheets("Delete.Rows")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
' you can change the starting row, right now its 4
    For row = 4 To lastRow
' store whats in col A in a temporary variable
        temp = Trim(CStr(.Range("A" & row).Value))
            If .Range("B" & row).Value = "" Then
                .Rows(row).ClearContents
' place temp variable value back in col A
                .Range("A" & row).Value = temp
            End If
    Next
End With
End Sub
Click to expand...
Sorry, I went with another answer, the code deleted value down and to right and left column A alone but deleted only if A had a value too. Thanks for the effort and the response.
 
kds14589
Moderator note:
Please, reread Forum Rules (from all You used Forum)

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
vletm said:
kds14589
Moderator note:
Please, reread Forum Rules (from all You used Forum)

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
Click to expand...
I did not realize the answer was to be posted too just the question. OK
 
You must log in or register to reply here.
Back
Top