Solved

Clear Contents From Specific Row

Posted on 2014-01-03
4
347 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
  • 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
 

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
 

Author Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This is about my first experience with programming Arduino.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now