Solved

Clear Contents From Specific Row

Posted on 2014-01-03
4
383 Views
Last Modified: 2014-01-04
I found this code on the web here - http://www.vbaexpress.com/forum/archive/index.php/t-31071.html - and assigned it to a command button. It works fine for the entire row but I want to clear contents from left of column E. In other words, clear contents from A to E only.


Dim rStart As Range, rLast As Range, rConstants As Range

 If Not TypeOf Selection Is Range Then Exit Sub

 Set rStart = Selection.Rows(1).EntireRow.Cells(1, 1)
 Set rLast = Selection.Rows(1).EntireRow.Cells(1, Selection.Parent.Columns.Count).End(xlToLeft)
 Set rConstants = Nothing
 On Error Resume Next
 Set rConstants = Range(rStart, rLast).SpecialCells(xlCellTypeConstants)
 On Error GoTo 0

 If Not rConstants Is Nothing Then
 Call rConstants.ClearContents
 End If

Thanks!
0
Comment
Question by:tracyms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 39755465
Find and change:

Set rLast = Selection.Rows(1).EntireRow.Cells(1, 5)
0
 
LVL 1

Author Comment

by:tracyms
ID: 39755493
Works great, thanks! I will award you the points. I'm curious if this will this work on specific cells? For example, if I wanted to clear contents in A-E but skip C? I have another spreadsheet that I may want to try this on.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 39756795
Sub test()
Dim rStart As Range, rLast As Range, rConstants As Range
Dim rStart2 As Range, rLast2 As Range, rConstants2 As Range

 If Not TypeOf Selection Is Range Then Exit Sub

 Set rStart = Selection.Rows(1).EntireRow.Cells(1, 1)
 Set rLast = Selection.Rows(1).EntireRow.Cells(1, 2)
 Set rStart2 = Selection.Rows(1).EntireRow.Cells(1, 4)
 Set rLast2 = Selection.Rows(1).EntireRow.Cells(1, 5)
 
 Set rConstants = Nothing
 Set rConstants2 = Nothing
 On Error Resume Next
 Set rConstants = Range(rStart, rLast).SpecialCells(xlCellTypeConstants)
 Set rConstants2 = Range(rStart2, rLast2).SpecialCells(xlCellTypeConstants)
 On Error GoTo 0

 If Not rConstants Is Nothing Then
 Call rConstants.ClearContents
 End If
 If Not rConstants2 Is Nothing Then
 Call rConstants2.ClearContents
 End If
 
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:tracyms
ID: 39756907
Thanks so much! Experts Exchange is awesome! :-)
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question