Hi all,

I would like to know the macros some of the basic routines like:

1. Flip/invert ALL values in range A and paste it into range B.
i.e.
Range("B1").value = Range("A65536").value
.....
Range("B65536").value = Range("A1").value

2. a) If all cells in a particular row is empty, then delete that
whole row.
b) If all cells in a particular range in a row is empty, then
delete the whole row.
For example, in range A2:A13 if A8 and A11 are empty, then
delete the entire rows 8 and 11.
For example, in range A15: A26 if A17 and A19 are empty,
then delte the entire rows 17 and 19.

3. If all cells in a particular column is empty, then delete the whole
column.

4. What would be the basic syntax to check each cell in a row...
Instead of "For each Cell Inthis Workbook.Worksheets",

a. I am looking for the syntax that will
Check for a particular value in each cell (256cells) )in a
row, perform a condition (I can manage this) and
then switch to next row, Check for a particular value in
each cell in that row, perform a condition etc... and
loop (65536 times) the same for all the other rows..

b. I am looking for the syntax that will
Check a particular value in each cell (65536 cells) in a
column, perform a condition and
then switch to next column, Check a particular value in each
cell in that column, perform a condition etc... and
loop for 256 columns..

Please let me know because I believe if I know this I will be able to
manage most of my issues with Excel VBA...
Any reference to any online material that would potentially answer my
questions would also greatly help.

Thanks, Kevin.

For #1:

Sub test()
lrow = Cells(Rows.Count, 1).End(xlUp).Row
x = 1
For i = lrow To 1 Step -1
Cells(x, 2).Value = Cells(i, 1).Value
x = x + 1
Next i
End Sub

--
Dan


On Oct 30, 1:25*pm, wrote:
Hi all,

I would like to know the macros some of the basic routines like:

1. Flip/invert ALL values in range A and paste it into range B.
i.e.
Range("B1").value = Range("A65536").value
....
Range("B65536").value = Range("A1").value

2. a) *If all cells in a particular row is empty, then delete that
whole row.
* * b) If all cells in a particular range in a row is empty, then
delete the whole row.
* * * * * * For example, in range A2:A13 if A8 and A11 are empty, then
delete the entire rows 8 and 11.
* * * * * * For example, in range A15: A26 if A17 and A19 are empty,
then delte the entire rows 17 and 19.

3. If all cells in a particular column is empty, then delete the whole
column.

4. What would be the basic syntax to check each cell in a row...
* * * * * Instead of "For each Cell Inthis Workbook.Worksheets",

* * *a. *I am looking for the syntax that will
* * * * *Check for a particular value in each cell (256cells) )in a
row, perform a condition (I can manage this) and
* * * * *then switch to next row, *Check for a particular value in
each cell in that row, perform a condition *etc... and
* * * * *loop (65536 *times) the same for all the other rows...

* * *b. *I am looking for the syntax that will
* * * * *Check a particular value in each cell (65536 cells) in a
column, perform a condition *and
* * * * *then switch to next column, *Check a particular value in each
cell in that column, perform a condition *etc... and
* * * * *loop for 256 columns..

Please let me know because I believe if I know this I will be able to
manage most of my issues with Excel VBA...
Any reference to any online material that would potentially answer my
questions would also greatly help.

Thanks, Kevin.

For #2:

Sub test2()
lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lrow To 1 Step -1
Set found = Rows(i).Find(What:="*")
If found Is Nothing Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub

--
Dan


On Oct 30, 1:25*pm, wrote:
Hi all,

I would like to know the macros some of the basic routines like:

1. Flip/invert ALL values in range A and paste it into range B.
i.e.
Range("B1").value = Range("A65536").value
....
Range("B65536").value = Range("A1").value

2. a) *If all cells in a particular row is empty, then delete that
whole row.
* * b) If all cells in a particular range in a row is empty, then
delete the whole row.
* * * * * * For example, in range A2:A13 if A8 and A11 are empty, then
delete the entire rows 8 and 11.
* * * * * * For example, in range A15: A26 if A17 and A19 are empty,
then delte the entire rows 17 and 19.

3. If all cells in a particular column is empty, then delete the whole
column.

4. What would be the basic syntax to check each cell in a row...
* * * * * Instead of "For each Cell Inthis Workbook.Worksheets",

* * *a. *I am looking for the syntax that will
* * * * *Check for a particular value in each cell (256cells) )in a
row, perform a condition (I can manage this) and
* * * * *then switch to next row, *Check for a particular value in
each cell in that row, perform a condition *etc... and
* * * * *loop (65536 *times) the same for all the other rows...

* * *b. *I am looking for the syntax that will
* * * * *Check a particular value in each cell (65536 cells) in a
column, perform a condition *and
* * * * *then switch to next column, *Check a particular value in each
cell in that column, perform a condition *etc... and
* * * * *loop for 256 columns..

Please let me know because I believe if I know this I will be able to
manage most of my issues with Excel VBA...
Any reference to any online material that would potentially answer my
questions would also greatly help.

Thanks, Kevin.

For #3:

Sub test3()
lcol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = lcol To 1 Step -1
Set found = Columns(i).Find(What:="*", _
searchOrder:=xlByColumns)
If found Is Nothing Then
Columns(i).EntireColumn.Delete
End If
Next i
End Sub

--
Dan


On Oct 30, 1:25*pm, wrote:
Hi all,

I would like to know the macros some of the basic routines like:

1. Flip/invert ALL values in range A and paste it into range B.
i.e.
Range("B1").value = Range("A65536").value
....
Range("B65536").value = Range("A1").value

2. a) *If all cells in a particular row is empty, then delete that
whole row.
* * b) If all cells in a particular range in a row is empty, then
delete the whole row.
* * * * * * For example, in range A2:A13 if A8 and A11 are empty, then
delete the entire rows 8 and 11.
* * * * * * For example, in range A15: A26 if A17 and A19 are empty,
then delte the entire rows 17 and 19.

3. If all cells in a particular column is empty, then delete the whole
column.

4. What would be the basic syntax to check each cell in a row...
* * * * * Instead of "For each Cell Inthis Workbook.Worksheets",

* * *a. *I am looking for the syntax that will
* * * * *Check for a particular value in each cell (256cells) )in a
row, perform a condition (I can manage this) and
* * * * *then switch to next row, *Check for a particular value in
each cell in that row, perform a condition *etc... and
* * * * *loop (65536 *times) the same for all the other rows...

* * *b. *I am looking for the syntax that will
* * * * *Check a particular value in each cell (65536 cells) in a
column, perform a condition *and
* * * * *then switch to next column, *Check a particular value in each
cell in that column, perform a condition *etc... and
* * * * *loop for 256 columns..

Please let me know because I believe if I know this I will be able to
manage most of my issues with Excel VBA...
Any reference to any online material that would potentially answer my
questions would also greatly help.

Thanks, Kevin.

On Oct 30, 1:12*pm, "Dan R." wrote:
For #3:

Sub test3()
* lcol = Cells(1, Columns.Count).End(xlToLeft).Column
* For i = lcol To 1 Step -1
* * Set found = Columns(i).Find(What:="*", _
* * * * * * * * * searchOrder:=xlByColumns)
* * If found Is Nothing Then
* * * Columns(i).EntireColumn.Delete
* * End If
* Next i
End Sub

--
Dan

On Oct 30, 1:25*pm, wrote:



Hi all,

I would like to know the macros some of the basic routines like:

1. Flip/invert ALL values in range A and paste it into range B.
i.e.
Range("B1").value = Range("A65536").value
....
Range("B65536").value = Range("A1").value

2. a) *If all cells in a particular row is empty, then delete that
whole row.
* * b) If all cells in a particular range in a row is empty, then
delete the whole row.
* * * * * * For example, in range A2:A13 if A8 and A11 are empty, then
delete the entire rows 8 and 11.
* * * * * * For example, in range A15: A26 if A17 and A19 are empty,
then delte the entire rows 17 and 19.

3. If all cells in a particular column is empty, then delete the whole
column.

4. What would be the basic syntax to check each cell in a row...
* * * * * Instead of "For each Cell Inthis Workbook.Worksheets",

* * *a. *I am looking for the syntax that will
* * * * *Check for a particular value in each cell (256cells) )in a
row, perform a condition (I can manage this) and
* * * * *then switch to next row, *Check for a particular value in
each cell in that row, perform a condition *etc... and
* * * * *loop (65536 *times) the same for all the other rows..

* * *b. *I am looking for the syntax that will
* * * * *Check a particular value in each cell (65536 cells) in a
column, perform a condition *and
* * * * *then switch to next column, *Check a particular value in each
cell in that column, perform a condition *etc... and
* * * * *loop for 256 columns..

Please let me know because I believe if I know this I will be able to
manage most of my issues with Excel VBA...
Any reference to any online material that would potentially answer my
questions would also greatly help.

Thanks, Kevin.- Hide quoted text -

- Show quoted text -

Hi Dan,

Thanks for your reply. Could you please throw some insight on 2b, 4a &
4b? Thanks again.

to modify your idea a little, i think this would be faster, as long as there are
no formulas evaluating to "" and there is always a header row:

Sub test4()
Dim ws As Worksheet
Dim lcol As Long
Dim i As Long
Set ws = Worksheets("Sheet1")
lcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
For i = lcol To 1 Step -1
With ws
If WorksheetFunction.CountA(.Columns(i)) = 0 Then
.Columns(i).EntireColumn.Delete
End If
End With
Next
End Sub


--


Gary

"Dan R." wrote in message
...
For #3:

Sub test3()
lcol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = lcol To 1 Step -1
Set found = Columns(i).Find(What:="*", _
searchOrder:=xlByColumns)
If found Is Nothing Then
Columns(i).EntireColumn.Delete
End If
Next i
End Sub

--
Dan


On Oct 30, 1:25 pm, wrote:
Hi all,

I would like to know the macros some of the basic routines like:

1. Flip/invert ALL values in range A and paste it into range B.
i.e.
Range("B1").value = Range("A65536").value
....
Range("B65536").value = Range("A1").value

2. a) If all cells in a particular row is empty, then delete that
whole row.
b) If all cells in a particular range in a row is empty, then
delete the whole row.
For example, in range A2:A13 if A8 and A11 are empty, then
delete the entire rows 8 and 11.
For example, in range A15: A26 if A17 and A19 are empty,
then delte the entire rows 17 and 19.

3. If all cells in a particular column is empty, then delete the whole
column.

4. What would be the basic syntax to check each cell in a row...
Instead of "For each Cell Inthis Workbook.Worksheets",

a. I am looking for the syntax that will
Check for a particular value in each cell (256cells) )in a
row, perform a condition (I can manage this) and
then switch to next row, Check for a particular value in
each cell in that row, perform a condition etc... and
loop (65536 times) the same for all the other rows..

b. I am looking for the syntax that will
Check a particular value in each cell (65536 cells) in a
column, perform a condition and
then switch to next column, Check a particular value in each
cell in that column, perform a condition etc... and
loop for 256 columns..

Please let me know because I believe if I know this I will be able to
manage most of my issues with Excel VBA...
Any reference to any online material that would potentially answer my
questions would also greatly help.

Thanks, Kevin.

These are very basic but they should get you started:

Sub test2b()
For Each cell In Range("A2:A13")
If cell = "" Then
cell.EntireRow.Delete
End If
Next cell
End Sub

Sub test4a()
lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lrow
Set found = Rows(i).Find(What:="dog")
If Not found Is Nothing Then
MsgBox found.Value & vbLf & found.Address
End If
Next i
End Sub

Sub test4b()
lcol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To lcol
Set found = Columns(i).Find(What:="dog", _
searchOrder:=xlByColumns)
If Not found Is Nothing Then
Range(found.Address) = "cat"
End If
Next i
End Sub

Here's a good website for reference:
http://www.cpearson.com/excel/topic.aspx

--
Dan


On Oct 30, 3:27*pm, wrote:
On Oct 30, 1:12*pm, "Dan R." wrote:





For #3:

Sub test3()
* lcol = Cells(1, Columns.Count).End(xlToLeft).Column
* For i = lcol To 1 Step -1
* * Set found = Columns(i).Find(What:="*", _
* * * * * * * * * searchOrder:=xlByColumns)
* * If found Is Nothing Then
* * * Columns(i).EntireColumn.Delete
* * End If
* Next i
End Sub

--
Dan

On Oct 30, 1:25*pm, wrote:

Hi all,

I would like to know the macros some of the basic routines like:

1. Flip/invert ALL values in range A and paste it into range B.
i.e.
Range("B1").value = Range("A65536").value
....
Range("B65536").value = Range("A1").value

2. a) *If all cells in a particular row is empty, then delete that
whole row.
* * b) If all cells in a particular range in a row is empty, then
delete the whole row.
* * * * * * For example, in range A2:A13 if A8 and A11 are empty, then
delete the entire rows 8 and 11.
* * * * * * For example, in range A15: A26 if A17 and A19 are empty,
then delte the entire rows 17 and 19.

3. If all cells in a particular column is empty, then delete the whole
column.

4. What would be the basic syntax to check each cell in a row...
* * * * * Instead of "For each Cell Inthis Workbook.Worksheets",

* * *a. *I am looking for the syntax that will
* * * * *Check for a particular value in each cell (256cells) )in a
row, perform a condition (I can manage this) and
* * * * *then switch to next row, *Check for a particular value in
each cell in that row, perform a condition *etc... and
* * * * *loop (65536 *times) the same for all the other rows..

* * *b. *I am looking for the syntax that will
* * * * *Check a particular value in each cell (65536 cells) in a
column, perform a condition *and
* * * * *then switch to next column, *Check a particular value in each
cell in that column, perform a condition *etc... and
* * * * *loop for 256 columns..

Please let me know because I believe if I know this I will be able to
manage most of my issues with Excel VBA...
Any reference to any online material that would potentially answer my
questions would also greatly help.

Thanks, Kevin.- Hide quoted text -

- Show quoted text -

Hi Dan,

Thanks for your reply. Could you please throw some insight on 2b, 4a &
4b? Thanks again.- Hide quoted text -

- Show quoted text -

On Oct 30, 1:56*pm, "Dan R." wrote:
These are very basic but they should get you started:

Sub test2b()
* For Each cell In Range("A2:A13")
* * If cell = "" Then
* * * cell.EntireRow.Delete
* * End If
* Next cell
End Sub

Sub test4a()
* lrow = Cells(Rows.Count, 1).End(xlUp).Row
* For i = 1 To lrow
* * Set found = Rows(i).Find(What:="dog")
* * If Not found Is Nothing Then
* * * MsgBox found.Value & vbLf & found.Address
* * End If
* Next i
End Sub

Sub test4b()
* lcol = Cells(1, Columns.Count).End(xlToLeft).Column
* For i = 1 To lcol
* * Set found = Columns(i).Find(What:="dog", _
* * * * * * * * * searchOrder:=xlByColumns)
* * If Not found Is Nothing Then
* * * Range(found.Address) = "cat"
* * End If
* Next i
End Sub

Here's a good website for reference:http://www.cpearson.com/excel/topic.aspx

--
Dan

On Oct 30, 3:27*pm, wrote:



On Oct 30, 1:12*pm, "Dan R." wrote:

For #3:

Sub test3()
* lcol = Cells(1, Columns.Count).End(xlToLeft).Column
* For i = lcol To 1 Step -1
* * Set found = Columns(i).Find(What:="*", _
* * * * * * * * * searchOrder:=xlByColumns)
* * If found Is Nothing Then
* * * Columns(i).EntireColumn.Delete
* * End If
* Next i
End Sub

--
Dan

On Oct 30, 1:25*pm, wrote:

Hi all,

I would like to know the macros some of the basic routines like:

1. Flip/invert ALL values in range A and paste it into range B.
i.e.
Range("B1").value = Range("A65536").value
....
Range("B65536").value = Range("A1").value

2. a) *If all cells in a particular row is empty, then delete that
whole row.
* * b) If all cells in a particular range in a row is empty, then
delete the whole row.
* * * * * * For example, in range A2:A13 if A8 and A11 are empty, then
delete the entire rows 8 and 11.
* * * * * * For example, in range A15: A26 if A17 and A19 are empty,
then delte the entire rows 17 and 19.

3. If all cells in a particular column is empty, then delete the whole
column.

4. What would be the basic syntax to check each cell in a row...
* * * * * Instead of "For each Cell Inthis Workbook.Worksheets",

* * *a. *I am looking for the syntax that will
* * * * *Check for a particular value in each cell (256cells) )in a
row, perform a condition (I can manage this) and
* * * * *then switch to next row, *Check for a particular value in
each cell in that row, perform a condition *etc... and
* * * * *loop (65536 *times) the same for all the other rows..

* * *b. *I am looking for the syntax that will
* * * * *Check a particular value in each cell (65536 cells) in a
column, perform a condition *and
* * * * *then switch to next column, *Check a particular value in each
cell in that column, perform a condition *etc... and
* * * * *loop for 256 columns..

Please let me know because I believe if I know this I will be able to
manage most of my issues with Excel VBA...
Any reference to any online material that would potentially answer my
questions would also greatly help.

Thanks, Kevin.- Hide quoted text -

- Show quoted text -

Hi Dan,

Thanks for your reply. Could you please throw some insight on 2b, 4a &
4b? Thanks again.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Dan, Thanks a lot... I'll try all those. It is definitely a very good
starting point to get the basics firm.

---
Kevin.