Macros for some basic routines...

[ucanalways]

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.

 

[Dan R.]

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 R.]

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 R.]

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

 

[ucanalways]

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












- Show quoted text -

Hi Dan,

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

 

[Gary Keramidas]

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

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 R.]

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

 

[ucanalways]

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






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