VBA Code help - change certain Rows...

Matty · Jul 21, 2011

Hi,

Given the following data set:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Data1</td><td>Day1</td><td>Day2</td><td>Day3</td><td>Day4</td><td>Day5</td><td>Day6</td><td>Day7</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>ABC</td><td style="text-align:right; ">2</td><td style="text-align:right; ">76</td><td style="text-align:right; ">53</td><td style="text-align:right; ">58</td><td style="text-align:right; ">80</td><td style="text-align:right; ">96</td><td style="text-align:right; ">30</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>DEF</td><td style="text-align:right; ">0
</td><td style="text-align:right; ">0
</td><td style="text-align:right; ">0
</td><td style="text-align:right; ">0
</td><td style="text-align:right; ">0
</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>GHI</td><td style="text-align:right; ">52</td><td style="text-align:right; ">39</td><td style="text-align:right; ">20</td><td style="text-align:right; ">72</td><td style="text-align:right; ">53</td><td style="text-align:right; ">33</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>ABC</td><td style="text-align:right; ">33</td><td style="text-align:right; ">97</td><td style="text-align:right; ">22</td><td style="text-align:right; ">74</td><td style="text-align:right; ">38</td><td style="text-align:right; ">91</td><td style="text-align:right; ">82</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>DEF</td><td style="text-align:right; ">12</td><td style="text-align:right; ">13</td><td style="text-align:right; ">16</td><td style="text-align:right; ">20</td><td style="text-align:right; ">33</td><td style="text-align:right; ">59</td><td style="text-align:right; ">42</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>GHI</td><td style="text-align:right; ">79</td><td style="text-align:right; ">50</td><td style="text-align:right; ">76</td><td style="text-align:right; ">86</td><td style="text-align:right; ">88</td><td style="text-align:right; ">12</td><td style="text-align:right; ">63</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>ABC</td><td style="text-align:right; ">2</td><td style="text-align:right; ">7</td><td style="text-align:right; ">10</td><td style="text-align:right; ">39</td><td style="text-align:right; ">100</td><td style="text-align:right; ">52</td><td style="text-align:right; ">97</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>DEF</td><td style="text-align:right; ">75</td><td style="text-align:right; ">98</td><td style="text-align:right; ">11</td><td style="text-align:right; ">9</td><td style="text-align:right; ">95</td><td style="text-align:right; ">21</td><td style="text-align:right; ">68</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>GHI</td><td style="text-align:right; ">41</td><td style="text-align:right; ">93</td><td style="text-align:right; ">72</td><td style="text-align:right; ">7</td><td style="text-align:right; ">80</td><td style="text-align:right; ">14</td><td style="text-align:right; ">49</td></tr></tbody></table>
I'm after some code which will convert any Cells in the "DEF" and "GHI" Rows from whatever is in them to 0. To illustrate, I have manually done this in the first "DEF" Row.

My list will grow and shrink, so this needs to be considered too (the Column count will remain constant).

Thanks in advance.

Matty

wigi · Jul 21, 2011

Hello Matty

Consider:

Code:

Sub zeroout()
    With [A1].CurrentRegion
        .AutoFilter 1, "DEF", xlOr, "GHI"
        .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).SpecialCells(12) = 0
        .AutoFilter
    End With
End Sub

hiker95 · Jul 21, 2011

Matty,

Sample raw data before the macro:

Excel Workbook

A

B

C

D

E

F

G

H

1

Data1

Day1

Day2

Day3

Day4

Day5

Day6

Day7

2

ABC

2

76

53

58

80

96

30

3

DEF

2

7

10

39

100

52

97

4

GHI

52

39

20

72

53

33

1

5

ABC

33

97

22

74

38

91

82

6

DEF

12

13

16

20

33

59

42

7

GHI

79

50

76

86

88

12

63

8

ABC

2

7

10

39

100

52

97

9

DEF

75

98

11

9

95

21

68

10

GHI

41

93

72

7

80

14

49

11

Sheet1

After the macro:

Excel Workbook

A

B

C

D

E

F

G

H

1

Data1

Day1

Day2

Day3

Day4

Day5

Day6

Day7

2

ABC

2

76

53

58

80

96

30

3

DEF

0

4

GHI

0

5

ABC

33

97

22

74

38

91

82

6

DEF

0

7

GHI

0

8

ABC

2

7

10

39

100

52

97

9

DEF

0

10

GHI

0

11

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub ChangeRows()
' hiker95, 07/21/2011
' http://www.mrexcel.com/forum/showthread.php?t=566146
Dim AH, a As Long, aa As Long
AH = Cells.Cells(1).CurrentRegion.Resize(, 8)
For a = LBound(AH) To UBound(AH)
  If AH(a, 1) = "DEF" Or AH(a, 1) = "GHI" Then
    For aa = 2 To 8
      AH(a, aa) = 0
    Next aa
  End If
Next a
Cells.Cells(1).CurrentRegion.Resize(, 8) = AH
End Sub

Then run the ChangeRows macro.

Matty · Jul 22, 2011

Thanks guys, but I have a problem: my real data set can be completely seperated by blank Rows which means the CurrentRegion method fails.

Is there something else that will work? I should point out that I don't want to use AutoFilter either for this.

Thanks,

Matty

wigi · Jul 22, 2011

Matty said:
Thanks guys, but I have a problem: my real data set can be completely seperated by blank Rows which means the CurrentRegion method fails.

Then you can use the UsedRange property of the sheet.

Obviously, a layout with completely empty rows is not what is thought of as good spreadsheet design, but it's your choice.

Matty said:
I should point out that I don't want to use AutoFilter either for this.

I do not understand that line of reasoning, but then use an approach similar to Hiker's proposal.

Matty · Jul 22, 2011

Hi wigi,

Code:

Then you can use the UsedRange property of the sheet.

If I use UsedRange, I get Object doesn't support this this property or method error.

Obviously, a layout with completely empty rows is not what is thought of as good spreadsheet design, but it's your choice.

It isn't really a traditional list I am working with; more a group of individual tables that have similar data where I want to wipe clean any none 0 values maintained in specific Rows.

I apologise for not making things more clear in the first place.

Thanks,

Matty

hiker95 · Jul 22, 2011

Matty,

Sample raw data before the updated macro:

Excel Workbook

A

B

C

D

E

F

G

H

1

Data1

Day1

Day2

Day3

Day4

Day5

Day6

Day7

2

ABC

2

76

53

58

80

96

30

3

DEF

2

7

10

39

100

52

97

4

5

GHI

52

39

20

72

53

33

1

6

ABC

33

97

22

74

38

91

82

7

DEF

12

13

16

20

33

59

42

8

GHI

79

50

76

86

88

12

63

9

ABC

2

7

10

39

100

52

97

10

11

DEF

75

98

11

9

95

21

68

12

GHI

41

93

72

7

80

14

49

13

Sheet1

After the updated macro:

Excel Workbook

A

B

C

D

E

F

G

H

1

Data1

Day1

Day2

Day3

Day4

Day5

Day6

Day7

2

ABC

2

76

53

58

80

96

30

3

DEF

0

4

5

GHI

0

6

ABC

33

97

22

74

38

91

82

7

DEF

0

8

GHI

0

9

ABC

2

7

10

39

100

52

97

10

11

DEF

0

12

GHI

0

13

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub ChangeRowsV2()
' hiker95, 07/22/2011
' http://www.mrexcel.com/forum/showthread.php?t=566146
Dim AH, a As Long, aa As Long, LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
AH = Range("A1:H" & LR)
For a = LBound(AH) To UBound(AH)
  If AH(a, 1) = "DEF" Or AH(a, 1) = "GHI" Then
    For aa = 2 To 8
      AH(a, aa) = 0
    Next aa
  End If
Next a
Range("A1:H" & LR) = AH
End Sub

Then run the ChangeRowsV2 macro.

wigi · Jul 22, 2011

That's why I wrote that UsedRange is a property of the sheet, not of a range.

Go with Hiker's approach by reading in the sheet's usedrange in an array.

Alternatively, do an autofilter on the different areas. This will give you the clue:

Code:

Sub ReplaceTitles()
    Dim ar As Range
    For Each ar In Sheets(1).UsedRange.Columns(1).SpecialCells(2, 1).Areas
        MsgBox ar.Address
    Next
End Sub

Matty · Jul 25, 2011

Many thanks to you both.

Hi hiker95,

I would like to better understand the solution you have provided in case I need to tweak it going forward. Here's my take on what I think it's doing:

Code:

Option Explicit
  Sub ChangeRowsV2()
  ' hiker95, 07/22/2011
  ' [URL]http://www.mrexcel.com/forum/showthread.php?t=566146[/URL]
  Dim AH, a As Long, aa As Long, LR As Long     'Declare variables to speed things along
  LR = Cells(Rows.Count, 1).End(xlUp).Row     'Establish last Row number based on Column A
  AH = Range("A1:H" & LR)     'Define Range of data
  For a = LBound(AH) To UBound(AH)     'Not totally sure I understand the necessity of this - kindly clarify   
    If AH(a, 1) = "DEF" Or AH(a, 1) = "GHI" Then     'Identify whether Column A contains either "DEF" or "GHI"
      For aa = 2 To 8     'Columns to be overwritten
        AH(a, aa) = 0     'Value to be overwritten with
      Next aa     'Move to next Column
    End If
  Next a     'Move to next Row
  Range("A1:H" & LR) = AH     'Overwrite all Rows where "DEF" or "GHI" exists with the value 0
  End Sub

Am I anywhere near close with this?

Other questions if you would be kind enough to answer:

Is there a reason you didn't declare a Variable for "AH"?
What's the purpose of LBound and UBound? I have never managed to get my head around its (there) uses.
Rather than updating the Cells while your macro cycles through them, it happens right at the end with this part: Range("A1:H" & LR) = AH How come this happens? Is it linked to point 2?

Sorry for all the questions - merely trying to broaden my understanding.

Thanks,

Matty

hiker95 · Jul 25, 2011

Matty,

See if this help you understand what is going on in memory. Working in memory is soooooooooo much faster than working in the worksheets.

Rich (BB code):

'Declare ALL variables
Option Explicit

Sub ChangeRowsV2()
' hiker95, 07/22/2011
' http://www.mrexcel.com/forum/showthread.php?t=566146

'AH is a Variant array
Dim AH, a As Long, aa As Long, LR As Long

'Because there are blank rows we need to find the last used row in column A
'                      1 stands for column A
'LR = 12
LR = Cells(Rows.Count, 1).End(xlUp).Row

'AH array = Range("A1:H" & LR)
'the array size is:
'  AH(1 to 12, 1 to 8)
'     1 to 12 rows deep, 1 = column A to 8 = column H
'Initalize the array
AH = Range("A1:H" & LR)

'a is a counter
'The LBound(AH) = 1, row 1, to UBound(AH) = 12, row 12
For a = LBound(AH) To UBound(AH)

  'cycle thru AH(1,1) thru AH(12,1)
  If AH(a, 1) = "DEF" Or AH(a, 1) = "GHI" Then
    
    'aa is a counter, 2 = column B, thru 8 = column H
    For aa = 2 To 8
      
      'set each element to 0
      AH(a, aa) = 0
    Next aa
  End If
Next a

'Write the AH array back to its original range
Range("A1:H" & LR) = AH
End Sub

VBA Code help - change certain Rows...

Matty

Well-known Member

Excel Facts

wigi

Well-known Member

hiker95

Well-known Member

Matty

Well-known Member

wigi

Well-known Member

Matty

Well-known Member

hiker95

Well-known Member

wigi

Well-known Member

Matty

Well-known Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

VBA Code help - change certain Rows...

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock