Paul Dada, You are great .. Thanks for the explanation will work on your
suggestions and get in touch with you again.
*Many Regards,*
*Rakesh*
On Mon, Feb 21, 2011 at 9:51 PM, Paul Schreiner <schreiner_p...@att.net>wrote:
> Let's step through the code:
>
> RowCounter = 1
>
>
>
> For ColName = 1 To 750
>
>
>
> If Sheets("1").Cells(2, ColName) = Sheets("Name").Cells(RowCounter,
> 1) Then
>
> Sheets("1").Cells(1, ColName) = 1
>
> RowCounter = RowCounter + 1
>
> Else
>
> Sheets("1").Cells(1, ColName) = 0
>
> End If
>
> 'Application.ScreenUpdating = False
>
> Next ColName
>
>
> You have two sheets named "1" and "Name".
> You set RowCounter = 1.
> The code looks through 750 columns in sheet named "1"
> and checks the value in row 2 to see if it matches the value in the sheet
> "Name"
> column 1, row (1) (from Rowcounter)
>
> If the cell doesn't match, it puts a "0" in row 1 of the column it's
> checking.
> If it MATCHES, then it puts a "1" in row 1, and increments RowCounter to
> begin checking
> for the next "name".
> That means that if a match is found in column 101,
> then you'll check columns 102-750 for the rest of the "names" in the list,
> but you'll no longer check 1-100 for ANY more names.
>
> Plus, the whole thing stops the first time a "name" is searched for and
> there is NO matches.
> Because, let's say that the FIRST name in the list
> (Sheets("Name").Cells(1,1))
> does not exist in row 2 of Sheets("1").
>
> Then the loop: For ColName = 1 to 750
> will run check all cells, and never find a match, so RowCounter will never
> be incremented,
> so no more names will be checked!
>
> Now, if the "Names" ALWAYS exist when you run this program,
> AND they appear in sheets("1") in the EXACT same order as they appear in
> sheets("Name")
> then it might work OK the FIRST time.
> But once you remove one of the columns, then the script will no longer
> complete successfully.
> ---------------------------
> Next, your loop:
>
> For Each c In Worksheets("1").Range("A1:a750").Cells
>
> If c.Value = 0 Then
>
> Columns(RowCounter).EntireColumn.Delete
>
> End If
>
> RowCounter = RowCounter + 1
>
> Next
>
>
>
> Since you're placing a 0/1 in the first ROW of sheets("1") then you should
> be looping through row 1, not column A.
>
> So your loop should read:
>
> For Each c In Worksheets("1").Range("A1:ABV1").Cells
>
> Now, instead of incrementing RowCounter, you could use the C object:
> C.Column like:
>
> Columns(C.Column).EntireColumn.Delete
>
> But this loop has another problem.
>
> In the first iteration:
> Column 1
> if Column 1 has a "0", then you delete it and check the next column.
> However, since you deleted column 1, then column 2 is now column 1.
> you increment your counter and check column 2.
> Which means that the "current" column 1 (formerly column 2) was never
> checked.
> If two adjacent columns happen to both have a "0", then you'll only delete
> the first.
>
> As in:
> A B C D E
> 1 1 0 0 1
> becomes:
> A B C D E
> 1 1 0 1
> when column C is deleted, the loop then checks column D
> in the next iteration.
>
> When deleting rows or columns, I prefer to work "backwards", like:
>
> For ColNo = 750 To 1 Step -1
> If Cells(1, ColNo).Value = 0 Then
> Cells(1, ColNo).EntireColumn.Delete
> End If
> Next
>
>
> hope this helps,
>
> Paul
> ------------------------------
> *From:* Daniel <dcolarde...@free.fr>
> *To:* excel-macros@googlegroups.com
> *Sent:* Mon, February 21, 2011 10:40:31 AM
> *Subject:* RE: $$Excel-Macros$$ CODE DOESN'T WORK
>
> The code is correct. Simply, there is no match and the test is always
> false. Can you post your data ?
>
> Daniel
>
>
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* rakesh kumar
> *Envoyé :* dimanche 20 février 2011 21:35
> *À :* excel-macros@googlegroups.com
> *Objet :* Re: $$Excel-Macros$$ CODE DOESN'T WORK
>
>
>
> *thanks Daniel Ji for the reply... here what I need and what the code
> doesn't do.*
>
>
>
> *This code runs as long as the Sheets("1").Cells(2, ColNumber) =
> Sheets("Name").Cells(RowCounter, 1) but doesn't do the job. I have some data
> with a lot of variables in sheet "1" of which I need few variable listed in
> Sheet "Name" Column A. I want to delete all column from Sheet 1 which
> doesn't match with variable listed in Sheet "Name" Column A.*
>
>
>
> *Thanks *
>
> On Sun, Feb 20, 2011 at 6:39 PM, Daniel <dcolarde...@free.fr> wrote:
>
> Hello,
>
> What is not working ? Do you get an error ?
>
> What are you trying to do ?
>
> Daniel
>
>
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* rakesh kumar
> *Envoyé :* dimanche 20 février 2011 11:16
> *À :* excel-macros@googlegroups.com
> *Objet :* $$Excel-Macros$$ CODE DOESN'T WORK
>
>
>
> Hi Dearests,
>
>
>
> Could anyone help me and make the below code work .... ?
>
>
>
> Dim GetVarCounts As Integer
>
> Dim ColName As Integer, RowCounter As Integer
>
> Dim oRg As Range
>
>
>
>
>
> RowCounter = 1
>
>
>
> For ColName = 1 To 750
>
>
>
> If Sheets("1").Cells(2, ColName) = Sheets("Name").Cells(RowCounter,
> 1) Then
>
> Sheets("1").Cells(1, ColName) = 1
>
> RowCounter = RowCounter + 1
>
> Else
>
> Sheets("1").Cells(1, ColName) = 0
>
> End If
>
> 'Application.ScreenUpdating = False
>
> Next ColName
>
>
>
> RowCounter = 1
>
> For Each c In Worksheets("1").Range("A1:a750").Cells
>
> If c.Value = 0 Then
>
> Columns(RowCounter).EntireColumn.Delete
>
> End If
>
> RowCounter = RowCounter + 1
>
> Next
>
>
>
>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
>
>
>
> --
> Rakesh kumar khowal
> +91 9540084660
> +91 9868532105
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Rakesh kumar khowal
+91 9540084660
+91 9868532105
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
