Solved

Clear Contents From Specific Row

Posted on 2014-01-03
4
389 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 36

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 36

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Simple Linear Regression
Six Sigma Control Plans

690 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