• 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


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

Restricting macro run to few columns


i have written macro so that it extracts data and replaces the existing contents of the whole sheet. But now i want to restrict this to only some columns say till column DI. How can i do this? Can anyone pls help me with codes.

Thanking you in advance,


The easiest way would be to limit the data that you are copying and then pasting the limited data into the sheet. Some code elements that might help with the copy:

To select the region around the active cell (cells with data in them):


To select all active cells below a cell (same as shift+end+down):

Range(Selection, Selection.End(xlDown)).Select

To select all active cells to right of a cell (same as shift+end+right):

Range(Selection, Selection.End(xlToRight)).Select


Thanks for the help. But its quite a big file and i am not sure this will help. I would like to share my file with you if you give me your mail ID

Thanking you,

Dee... this also depends on the structure of your data. For eg. if you data source data has more columns than up to DI, then you need to stop importing those columns thru macro code.

Here is a rough algorithm for your need. You can easily morph this in to VBA...

for each row in source data

for each column in that row

if the column number is less than 'DI'

replace target sheet row 'i' column 'j'


end if

next column


next row

remove data in all the balance rows after i until end of sheet
Hey Chandoo, thanks for the help.

In both the cases (source data & In-work file) the data is there till DI. In In-work file i have lot of formula which pulls data from some other file from share point site.Whenever i run macro it is removing the data in the cells beyond DI.


Can you please suggest where i can upload the file? i don't have any idea about it...

Thank you
Here is the codes...

Dim Ftwbook As Workbook

Dim Thiswbook As Workbook

Dim LastPM, LastCell, RIndex, counter As Integer

Dim Wksheet As Worksheet

Dim Flatfilename As String

Dim Filewithmacro As String

Application.ScreenUpdating = False

Flatfilename = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", Title:="Please choose a file")

Filewithmacro = ThisWorkbook.Name

Set Ftwbook = Workbooks.Open(Filename:=Flatfilename, ReadOnly:=yes)

Set Thiswbook = Workbooks(Filewithmacro)

ThisWorkbook.Worksheets("Project Managers").Activate


LastCell = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

ReDim PM(LastCell) As String

For counter = 0 To LastCell - 1

PM(counter) = ActiveCell.Value

ActiveCell.Offset(1, 0).Activate

Next counter

Thiswbook.Worksheets("Project List").Activate

Range("A4", Range("A4").SpecialCells(xlCellTypeLastCell).Address).Rows.ClearContents


Ftwbook.Worksheets("Flat File").Select


LastPM = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

RIndex = 4

For counter = 0 To LastPM - 1

For Index = 0 To LastCell - 1

If ActiveCell.Value = PM(Index) Then


Thiswbook.Worksheets("Project List").Activate

ActiveSheet.Paste Destination:=Range(Cells(RIndex, 1), Cells(RIndex, 1))

Ftwbook.Worksheets("Flat File").Activate

RIndex = RIndex + 1

Exit For


End If

Next Index

ActiveCell.Offset(1, 0).Activate

Next counter

Application.CutCopyMode = False

Ftwbook.Close SaveChanges = no

Application.ScreenUpdating = True

Call LastRowofPM

End Sub


Pls help me in this....
This line:

Range("A4", Range("A4").SpecialCells(xlCellTypeLastCell).Address).Rows.ClearContents

is the problem. It clears the whole row.

Try something like:

Range ("A4", Range("DI" & LastCell)).ClearContents

(I haven't checked it)
HI TessaES

Thank you very much for your help.

I tried with this.Here the problem is some rows will be missed from that of the source file.

And one more issue is after column DI i have formulas which links to the share point site but when i run the macro this link gets disconnected and automatically takes from my desktop :(
Hi Dee,

Conversations seems to have moved on a bit but if you still want to put your file up you could use skydrive:


Its free and you can store 25 GB on there. I use it for additional back up and filesharing with clients / accountants etc.

Please check my file here....


Thank you very much