• 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.

I have data in Excel i want clear the data in particular range

Hi Abhijeet

Well this would depend on weather you want your range to be static or dynamic. Also I guess when you say clear do you mean the contents on the cells (values) or the format too.

For a static range, something like this.

Code: 
[a1:a10].clearcontents

for a more dynamic range

Code: 
[a1].currentregion.clearcontents

if you wanted to clear the formatting replace clearcontents with just clear.

The qualification on the second bit of code is that it assumes your data has no column or Rows which are entirely blank.

If you wanted to clear a whole sheet something like

Code: 
Cells.ClearContents


Take care

Smallman
 
Hi
I have this macro i have to use in this code clear cells of range after this macro run the i want to run the clear cells macro please tell me
Option Explicit
Sub CopyData()
Dim i As Integer, lastRow As Long
Dim PstRng As Range

lastRow = Range("A" & Rows.Count).End(xlUp).Row
Sheet2.[2:65536].EntireRow.Delete

For i = 1 To 256
Set PstRng = Sheet2.Range("1:1").Find(Cells(1, i).Value, [A1], xlValues, xlWhole)
If Not PstRng Is Nothing Then
Range(Cells(2, i), Cells(lastRow, i)).Copy PstRng.Offset(1, 0)
End If
Next i
MsgBox "Done"
End Sub
 
This macro looks a lot like one you posted previously, which I showed you how to improve here:
http://chandoo.org/forum/threads/modified-the-macro.15827/#post-95598

Also, you need to be clearer about which cells you want to clear. You should say "Clear the cells that I copied from", or "Clear the cells on Sheet2", or something like that. Since you weren't precise, I will guess that you want to clear the cells your are copying from (otherwise, why paste them??).

Modifying the macro that I previously linked to:
Code: 
Sub CopyData()

Dim PstRng As Range
Dim CopyRange As Range

Set CopyRange = Sheet1.UsedRange
With ActiveSheet

   'Where are we copying to  
  Set PstRng = Intersect(.UsedRange, .Range("1:1"))
   'Clear old data  
  .Range("2:" & .Rows.Count).EntireRow.ClearContents
   'Copy via Filter  
  CopyRange.AdvancedFilter xlFilterCopy, , PstRng
End With
'Clear contents of cells
CopyRange.ClearContents
MsgBox "Done"
End Sub

Final thoughts
  1. If this doesn't work, please indicate precisely what is wrong. Saying "It's not working" is the least helpful.
  2. When you start a thread, try to give as much information as possible. Notice in this thread, you started with a single sentence, and then later posted the macro. Posting the macro you were using would have saved a step.
 
Hi Abhijeet,

Could you please explain what you are trying to achieve with this code.

Code: 
Option Explicit
Sub CopyData()
    Dim i As Integer, lastRow As Long
    Dim PstRng As Range
  
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Sheet2.[2:65536].EntireRow.Delete
  
        For i = 1 To 256
        Set PstRng = Sheet2.Range("1:1").Find(Cells(1, i).Value, [A1], xlValues, xlWhole)
            If Not PstRng Is Nothing Then
                Range(Cells(2, i), Cells(lastRow, i)).Copy PstRng.Offset(1, 0)
            End If
        Next i
    MsgBox "Done"
End Sub

P.S. - Saw Luke Sir's post after my reply.
 
@ThrottleWorks
In a long, round-about way, it's copying 1 cell at a time from Sheet1, row 2, to Sheet2, matching up column headers. :confused: This task can be accomplished much faster by either using an Advanced Filter, or HLOOKUP formulas in the sheet. :DD
 
Luke M said:
This macro looks a lot like one you posted previously, which I showed you how to improve here:
http://chandoo.org/forum/threads/modified-the-macro.15827/#post-95598

Also, you need to be clearer about which cells you want to clear. You should say "Clear the cells that I copied from", or "Clear the cells on Sheet2", or something like that. Since you weren't precise, I will guess that you want to clear the cells your are copying from (otherwise, why paste them??).

Modifying the macro that I previously linked to:
Code: 
Sub CopyData()

Dim PstRng As Range
Dim CopyRange As Range

Set CopyRange = Sheet1.UsedRange
With ActiveSheet

   'Where are we copying to 
  Set PstRng = Intersect(.UsedRange, .Range("1:1"))
   'Clear old data 
  .Range("2:" & .Rows.Count).EntireRow.ClearContents
   'Copy via Filter 
  CopyRange.AdvancedFilter xlFilterCopy, , PstRng
End With
'Clear contents of cells
CopyRange.ClearContents
MsgBox "Done"
End Sub

Final thoughts
  1. If this doesn't work, please indicate precisely what is wrong. Saying "It's not working" is the least helpful.
  2. When you start a thread, try to give as much information as possible. Notice in this thread, you started with a single sentence, and then later posted the macro. Posting the macro you were using would have saved a step.
Click to expand...
Luke M ur macro work perfect i want clear all data in sheet1& sheet2
 
I have to use this macro for different data so i have to delete manually for each time.so i am ask for clear cell data macro
 
At which number do you want to delete all the data? Parenthesis indicates what process currently controls.
  1. Headers are in Sheet2 (manual)
  2. Data is Sheet1 (manual)
  3. Any previous copied data in sheet2 is cleared (macro)
  4. New copy from Sheet1 (macro)
  5. Sheet1 is cleared (macro)
If we clear Sheet2 at end of macro, you won't be able to see the data that just got copied, and this becomes useless. If you are constantly changing the data in Sheet1, I would propose forgetting the macro altogether and just using a quick HLOOKUP formula, like:
=HLOOKUP(A$1,Sheet1!$1:$2,2,FALSE)
 
Luke M 1st Run macro give data in sheet 2 Then i run 2nd macro that clear data except sheet2 headers rest of data means sheet1 all data sheet 2 data from row 2 below datai want clear. Two Different buttons i want for macro to run
 
Luke M said:
At which number do you want to delete all the data? Parenthesis indicates what process currently controls.
  1. Headers are in Sheet2 (manual)
  2. Data is Sheet1 (manual)
  3. Any previous copied data in sheet2 is cleared (macro)
  4. New copy from Sheet1 (macro)
  5. Sheet1 is cleared (macro)
If we clear Sheet2 at end of macro, you won't be able to see the data that just got copied, and this becomes useless. If you are constantly changing the data in Sheet1, I would propose forgetting the macro altogether and just using a quick HLOOKUP formula, like:
=HLOOKUP(A$1,Sheet1!$1:$2,2,FALSE)
Click to expand...
Data is huge range so Formula i think not work proper it will take time to get the result
 
Abhijeet said:
Luke M 1st Run macro give data in sheet 2 Then i run 2nd macro that clear data except sheet2 headers rest of data means sheet1 all data sheet 2 data from row 2 below datai want clear. Two Different buttons i want for macro to run
Click to expand...
Ah, a 2nd macro. That was not clear before. In that case, something like this is fine:
Code: 
Sub CopyData()

Dim PstRng As Range
Dim CopyRange As Range

Set CopyRange = Sheet1.UsedRange
With ActiveSheet

   'Where are we copying to
 Set PstRng = Intersect(.UsedRange, .Range("1:1"))
   'Clear old data
 .Range("2:" & .Rows.Count).EntireRow.ClearContents
   'Copy via Filter
 CopyRange.AdvancedFilter xlFilterCopy, , PstRng
End With
'Clear contents of cells
CopyRange.ClearContents
MsgBox "Done"
End Sub

'This the the 2nd macro
Sub ClearData()
Sheet1.Cells.ClearContents
With Sheet2
    .Range("2:" & .Rows.Count).ClearContents
End With
End Sub

Abhijeet said:
Data is huge range so Formula i think not work proper it will take time to get the result
Click to expand...
From your previous macro, it looks like it's only transferring 256 cells, which would need 256 formulas, which is not intensive for XL.
 
Hi Luke M

I have one problem while use Advanced Filter.Attach file what i want i mention.From Sheet 1 split the data In Basic,Enh,OT Sheets base on Sheet 1 data.Data is huge so please help give me macro
 

Attachments

  • New Data.xlsx
    10.4 KB · Views: 2
Using the code from Ron's site:
http://www.rondebruin.nl/win/s3/win006_5.htm

His macro easily splits the data up to all the different sheets, and can create a new sheet if needed. Make sure to read through all the comments to make sure you understand what is going on.
 

Attachments

  • New Data Macro.xlsm
    30.5 KB · Views: 11
You must log in or register to reply here.
Back
Top