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
